Postgresql COALESCE性能问题

发布时间：2020-12-13 16:01:05 所属栏目：百科来源：网络整理

导读：我在 Postgresql中有这个表： 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 = ?

我在 Postgresql中有这个表：

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   0

explain 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 null

explain 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!