sql - Query optimization when using a JSON field -
running postgresql 9.6.4 on laptop, have table called node has primary key id field , properties::jsonb field.
i have gin index setup on properties field.
when run query:
select n.* node n node_type_id = '2' , properties @> '{"slug":"wild-castles"}'::jsonb order n.id asc offset 0 limit 10; on ~5m rows table takes 20 seconds answer. looking explain plan found out query optimizer first sorting table primary key , filtering properties field:
limit (cost=0.56..1517.94 rows=10 width=154) -> index scan using node_pkey on node n (cost=0.56..739571.11 rows=4874 width=154) filter: ((properties @> '{"slug": "wild-castles"}'::jsonb) , ((node_type_id)::text = '2'::text)) but when remove ordering i'm seeing optimizer using index expected:
select n.* node n node_type_id = '2' , properties @> '{"slug":"wild-castles"}'::jsonb offset 0 limit 10; limit (cost=93.77..127.10 rows=10 width=154) -> bitmap heap scan on node n (cost=93.77..16338.56 rows=4874 width=154) recheck cond: (properties @> '{"slug": "wild-castles"}'::jsonb) filter: ((node_type_id)::text = '2'::text) -> bitmap index scan on node_ix02 (cost=0.00..92.55 rows=4874 width=0) index cond: (properties @> '{"slug": "wild-castles"}'::jsonb) also, simple where properties @> '{"slug":"wild-castles"}'::jsonb behaves expected:
explain select n.* node n properties @> '{"slug":"wild-castles"}'::jsonb ; bitmap heap scan on node n (cost=93.77..16326.38 rows=4874 width=154) recheck cond: (properties @> '{"slug": "wild-castles"}'::jsonb) -> bitmap index scan on node_ix02 (cost=0.00..92.55 rows=4874 width=0) index cond: (properties @> '{"slug": "wild-castles"}'::jsonb) so guess i'm wondering why optimizer not use index filter out rows first , order them id field?
change planner method configuration , force planer not seqscan
eg
set enable_seqscan = off; select n.* node n node_type_id = '2' , properties @> '{"slug":"wild-castles"}'::jsonb order n.id asc offset 0 limit 10; 
Comments
Post a Comment