这是对Postgres 9.1.6的慢查询,即使最大计数为2,两行已经由主键标识:( 4.5秒)
EXPLAIN ANALYZE SELECT COUNT(*) FROM tbl WHERE id IN ('6d48fc431d21','d9e659e756ad') AND data ? 'building_floorspace' AND data ?| 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) AND (data ?| '{elec_mean_monthly_use,gas_mean_monthly_use}'::text[]))
Filter: ((id)::text = ANY ('{6d48fc431d21,d9e659e756ad}'::text[]))
Total runtime: 4457.948 ms
(5 rows)
嗯,也许如果我先用主键部分做一个子查询…… :(不,仍然是4.5秒)
EXPLAIN ANALYZE SELECT COUNT(*) FROM ( SELECT * FROM tbl WHERE id IN ('6d48fc431d21','d9e659e756ad') ) AS t WHERE data ? 'building_floorspace' AND data ?| 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) AND (data ?| '{elec_mean_monthly_use,d9e659e756ad}'::text[]))
Total runtime: 4854.220 ms
(5 rows)
如何防止Postgres内联子查询？
背景：我有一个使用hstore和GiST index的Postgres 9.1表.
我认为OFFSET 0是更好的方法,因为它更明显是一个黑客表明奇怪的事情正在发生,并且我们不太可能改变围绕OFFSET 0的优化器行为…希望CTE在某些时候可以变得可以内联.以下解释是为了完整性;使用Seamus的答案.
对于不相关的子查询,您可以利用Postgresql拒绝内联查询术语来将您的查询重新定义为：
WITH t AS (
SELECT * FROM tbl WHERE id IN ('6d48fc431d21','d9e659e756ad')
)
SELECT COUNT(*)
FROM t
WHERE data ? 'building_floorspace'
AND data ?| ARRAY['elec_mean_monthly_use','gas_mean_monthly_use'];
这与OFFSET 0 hack有很多相同的效果,就像偏移0 hack利用Pg优化器中的怪癖一样,人们用它来解决Pg缺乏查询提示……将它们用作查询提示.
