You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
with data as (
select
i,
substr('8000012000345987243985743285723945798274359824375287435098432759284375843759824375423095208435019843509283475082437592843859432798572430985720984375098432759384750432986772437540958724385743985721098437520984375',
i,
1) as tel
from
generate_series(1, length('8000012000345987243985743285723945798274359824375287435098432759284375843759824375423095208435019843509283475082437592843859432798572430985720984375098432759384750432986772437540958724385743985721098437520984375')) as g(i)
)
insert
into
json_test(body)
select
('{"specCode": {"name": "Telephone Number", "text": "TEL_NUM"}, "specValue": {"code": null, "text":"' || tel || '"}}')::json
from
data
returning *;
--explain analyze
select
body
from
json_test
where
body->'specValue'->>'text' = '1';
create INDEX ON "json_test" using btree ((body -> 'specValue' ->>'text'));
This should create a table with a JSONB column.
Generate some random data and query a field within the JSON data (explain commented out).
At the end, there is the code to create a BTREE index on the field in the where clause (condition in where is the same as the index).
With the index, you can use the explain statement from above and see that the index should be used.
Such queries are not covered by the index advisor.
The text was updated successfully, but these errors were encountered:
There are 2 limitations here explaining the behavior. First, pg_qualstats itself for now only tracks "simple" quals, e.g. things of the form column operator column or column operator constant. So with your example there's unfortunately nothing stored by pg_qualstats that the advisor could try to handle.
Then, even if more complex quals were handled by pg_qualstats, the advisor for now only tries to suggest indexes on one or multiple columns, but nothing more complex. So functional indexes wouldn't be handled anyway, same for partial indexes.
Clearly things could be improved, and I will try to look at it. But right now I'm not really sure of how exactly we could track complex expression. For instance if there's a function call using multiple columns, trying to attach the expr to all columns wouldn't be easy.
I butchered together some SQL statements :)
CREATE TABLE json_test(body jsonb);
create INDEX ON "json_test" using btree ((body -> 'specValue' ->>'text'));
This should create a table with a JSONB column.
Generate some random data and query a field within the JSON data (explain commented out).
At the end, there is the code to create a BTREE index on the field in the where clause (condition in where is the same as the index).
With the index, you can use the explain statement from above and see that the index should be used.
Such queries are not covered by the index advisor.
The text was updated successfully, but these errors were encountered: