postgresql - How can I prevent Postgres from inlining a subquery? -

postgresql - How can I prevent Postgres from inlining a subquery? -

here's slow query on postgres 9.1.6, though maximum count 2, both rows identified primary keys: (4.5 seconds)

explain analyze select count(*) tbl id in ('6d48fc431d21', 'd9e659e756ad') , info ? 'building_floorspace' , info ?| array['elec_mean_monthly_use', 'gas_mean_monthly_use']; query plan ---------------------------------------------------------------------------------------------------------------------------------------------------- aggregate (cost=4.09..4.09 rows=1 width=0) (actual time=4457.886..4457.887 rows=1 loops=1) -> index scan using idx_tbl_on_data_gist on tbl (cost=0.00..4.09 rows=1 width=0) (actual time=4457.880..4457.880 rows=0 loops=1) index cond: ((data ? 'building_floorspace'::text) , (data ?| '{elec_mean_monthly_use,gas_mean_monthly_use}'::text[])) filter: ((id)::text = ('{6d48fc431d21,d9e659e756ad}'::text[])) total runtime: 4457.948 ms (5 rows)

hmm, maybe if subquery primary key part first...: (nope, still 4.5+ seconds)

explain analyze select count(*) ( select * tbl id in ('6d48fc431d21', 'd9e659e756ad') ) t info ? 'building_floorspace' , info ?| array['elec_mean_monthly_use', 'gas_mean_monthly_use']; query plan ---------------------------------------------------------------------------------------------------------------------------------------------------- aggregate (cost=4.09..4.09 rows=1 width=0) (actual time=4854.170..4854.171 rows=1 loops=1) -> index scan using idx_tbl_on_data_gist on tbl (cost=0.00..4.09 rows=1 width=0) (actual time=4854.165..4854.165 rows=0 loops=1) index cond: ((data ? 'building_floorspace'::text) , (data ?| '{elec_mean_monthly_use,gas_mean_monthly_use}'::text[])) filter: ((id)::text = ('{6d48fc431d21,d9e659e756ad}'::text[])) total runtime: 4854.220 ms (5 rows)

how can prevent postgres inlining subquery?

background: have postgres 9.1 table using hstore , gist index on it.

i think offset 0 improve approach since it's more hack showing weird going on, , it's unlikely we'll ever alter optimiser behaviour around offset 0 ... wheras ctes become inlineable @ point. next explanation completeness's sake; utilize seamus's answer.

for uncorrelated subqueries can exploit postgresql's refusal inline with query terms rephrase query as:

with t ( select * tbl id in ('6d48fc431d21', 'd9e659e756ad') ) select count(*) t info ? 'building_floorspace' , info ?| array['elec_mean_monthly_use', 'gas_mean_monthly_use'];

this has much same effect offset 0 hack, , offset 0 hack exploits quirks in pg's optimizer people utilize around pg's lack of query hints ... using them query hints.

postgresql indexing subquery inlining