CREATE table my_table( ID bigint NOT NulL,value bigint,CONSTRAINT my_table_pkey PRIMARY KEY (ID));
my_table中有大约50000行.
问题是,为什么查询:
SELECT * FROM my_table WHERE ID = COALESCE(null,ID) and value = ?
比这个慢:
SELECT * FROM my_table WHERE value = ?
除了在app-layer中优化查询字符串之外,还有其他解决方案吗?
编辑:实际上,问题是如何重写查询select * from my_table where ID = coalesce(?,ID)和value =?最坏情况下的表现不低于select * from my_table,其中value =?在Postgresql 9.0中
解决方法 尝试重写表单的查询SELECT * FROM my_table WHERE value = ? AND (? IS NulL OR ID = ?)
从我自己的快速测试
INSERT INTO my_table select generate_serIEs(1,50000),1;UPDATE my_table SET value = ID%17;CREATE INDEX val_IDx ON my_table(value);VACUUM ANALYZE my_table;\set IDval 17\set pval 0explain analyze SELECT * FROM my_table WHERE value = :pval AND (:IDval IS NulL OR ID = :IDval);Index Scan using my_table_pkey on my_table (cost=0.00..8.29 rows=1 wIDth=16) (actual time=0.034..0.035 rows=1 loops=1) Index Cond: (ID = 17) Filter: (value = 0) Total runtime: 0.064 ms\set IDval nullexplain analyze SELECT * FROM my_table WHERE value = :pval AND (:IDval IS NulL OR ID = :IDval);Bitmap Heap Scan on my_table (cost=58.59..635.62 rows=2882 wIDth=16) (actual time=0.373..1.594 rows=2941 loops=1) Recheck Cond: (value = 0) -> Bitmap Index Scan on valIDx (cost=0.00..57.87 rows=2882 wIDth=0) (actual time=0.324..0.324 rows=2941 loops=1) Index Cond: (value = 0) Total runtime: 1.811 ms总结
以上是内存溢出为你收集整理的Postgresql COALESCE性能问题全部内容,希望文章能够帮你解决Postgresql COALESCE性能问题所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)