postgresql 视图中的合并未使用正确的联接顺序

以下查询工作正常，即使用索引和右连接顺序和方法

pgdb=> explain analyze SELECT ces.cesp, cm.cpk, ces.hmnc, pip.pn, cm.df,
pgdb->     COALESCE(cm.mid, 'CUST'::character varying) AS mid
pgdb->    FROM oc.ces ces
pgdb->      JOIN op.pip pip ON pip.pip_pk = ces.pip_fk
pgdb->      LEFT JOIN oc.cm cm ON cm.cpk = ces.cm_fk AND cm.df = '0'::bpchar
pgdb->   WHERE 1 = 1 AND ces.edt > '2016-11-30'::date AND ces.flg = true and mid = 'C123';
                                                                                          QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
 Nested Loop  (cost=1.53..856.50 rows=2 width=192) (actual time=9.439..10.322 rows=3 loops=1)
   ->  Nested Loop  (cost=1.12..855.64 rows=2 width=222) (actual time=8.159..8.171 rows=3 loops=1)
         ->  Index Scan using ix_cm_mid on cm cm  (cost=0.43..8.45 rows=1 width=82) (actual time=5.068..5.069 rows=1 loops=1)
               Index Cond: ((mid)::text = 'C123'::text)
               Filter: (df = '0'::bpchar)
         ->  Index Scan using fk_ces_cm_n on ces ces  (cost=0.69..846.48 rows=71 width=205) (actual time=3.087..3.097 rows=3 loops=1)
               Index Cond: (cm_fk = cm.cpk)
               Filter: (flg AND (edt > '2016-11-30'::date))
   ->  Index Scan using pk_pip on pip pip  (cost=0.41..0.43 rows=1 width=83) (actual time=0.713..0.714 rows=1 loops=3)
         Index Cond: (pip_pk = ces.pip_fk)
 Planning Time: 0.612 ms
 Execution Time: 10.388 ms
(12 rows)

但是，当同一个查询被放置在视图中时，它使用了不同的计划，其中它改变了连接顺序，并且不能有效地工作。

pgdb=> create or replace view public.v1 as
pgdb->  SELECT ces.cesp, cm.cpk, ces.hmnc, pip.pn, cm.df,
pgdb->     COALESCE(cm.mid, 'CUST'::character varying) AS mid
pgdb-> --    cm.mid
pgdb->    FROM oc.ces ces
pgdb->      JOIN op.pip pip ON pip.pip_pk = ces.pip_fk
pgdb->      LEFT JOIN oc.cm cm ON cm.cpk = ces.cm_fk AND cm.df = '0'::bpchar
pgdb->   WHERE 1 = 1 AND ces.edt > '2016-11-30'::date AND ces.flg = true;
CREATE VIEW
pgdb=> explain analyze  select * from public.v1 where mid = 'C123';
                                                                               QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------
------------
 Gather  (cost=1000.97..4676008.20 rows=2 width=192) (actual time=109844.932..150085.920 rows=3 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Nested Loop  (cost=0.97..4675008.00 rows=1 width=192) (actual time=128845.440..150074.647 rows=1 loops=3)
         ->  Nested Loop Left Join  (cost=0.56..4675007.57 rows=1 width=222) (actual time=128845.401..150074.599 rows=1 loops=3)
               Filter: ((COALESCE(cm.mid, 'CUST'::character varying))::text = 'C123'::text)
               Rows Removed by Filter: 2348376
               ->  Parallel Seq Scan on ces ces  (cost=0.00..2467721.06 rows=2908281 width=205) (actual time=1.027..7765.214 rows=2348377 loops=3)
                     Filter: (flg AND (edt > '2016-11-30'::date))
                     Rows Removed by Filter: 5502785
               ->  Index Scan using pk_cm on cm cm  (cost=0.56..0.75 rows=1 width=82) (actual time=0.060..0.060 rows=1 loops=7045130)
                     Index Cond: (cpk = ces.cm_fk)
                     Filter: (df = '0'::bpchar)
                     Rows Removed by Filter: 0
         ->  Index Scan using pk_pip on pip pip  (cost=0.41..0.43 rows=1 width=83) (actual time=0.039..0.039 rows=1 loops=3)
               Index Cond: (pip_pk = ces.pip_fk)
 Planning Time: 0.656 ms
 Execution Time: 150086.004 ms
(18 rows)

但是当不使用coalese时，同样的观点也很好。由于合并，无法在此行为差异下，这是postgresql 11.17。请指示。

pgdb=> create or replace view public.v1 as
pgdb->  SELECT ces.cesp, cm.cpk, ces.hmnc, pip.pn, cm.df,
pgdb-> --    COALESCE(cm.mid, 'CUST'::character varying) AS mid
pgdb->     cm.mid
pgdb->    FROM oc.ces ces
pgdb->      JOIN op.pip pip ON pip.pip_pk = ces.pip_fk
pgdb->      LEFT JOIN oc.cm cm ON cm.cpk = ces.cm_fk AND cm.df = '0'::bpchar
pgdb->   WHERE 1 = 1 AND ces.edt > '2016-11-30'::date AND ces.flg = true;
CREATE VIEW
pgdb=> explain analyze select * from public.v1 where mid = 'C123';
                                                                                          QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
 Nested Loop  (cost=1.53..856.50 rows=2 width=175) (actual time=0.069..0.093 rows=3 loops=1)
   ->  Nested Loop  (cost=1.12..855.64 rows=2 width=222) (actual time=0.055..0.063 rows=3 loops=1)
         ->  Index Scan using ix_cm_mid on cm cm  (cost=0.43..8.45 rows=1 width=82) (actual time=0.028..0.029
rows=1 loops=1)
               Index Cond: ((mid)::text = 'C123'::text)
               Filter: (df = '0'::bpchar)
         ->  Index Scan using fk_ces_cm_n on ces ces  (cost=0.69..846.48 rows=71 width=205) (actual
time=0.024..0.030 rows=3 loops=1)
               Index Cond: (cm_fk = cm.cpk)
               Filter: (flg AND (edt > '2016-11-30'::date))
   ->  Index Scan using pk_pip on pip pip  (cost=0.41..0.43 rows=1 width=83) (actual time=0.009..0.009 rows=1 loops=3)
         Index Cond: (pip_pk = ces.pip_fk)
 Planning Time: 0.740 ms
 Execution Time: 0.155 ms
(12 rows)

这和索引 * 可以使用 * 时的规则一样简单
对于具有相等 predicate 的索引列，可以使用索引

explain 
select * from t where id = '1';

Index Scan using t_idx on t  (cost=0.29..8.30 rows=1 width=105)
  Index Cond: (id = '1'::text)

如果列在某些函数调用中使用，如在coalesce中的情况index不能使用。一个 * 表的顺序扫描 * 是结果，你观察到的性能下降。

explain 
select * from t where coalesce(id,'x') = '1';

Seq Scan on t  (cost=0.00..298.00 rows=50 width=105)
  Filter: (COALESCE(id, 'x'::text) = '1'::text)

你必须定义一个 * function based * index，其表达式与使用Index Scan的 predicate 相同：

create index t_idx2 on t(coalesce(id,'x'));  

explain 
select * from t where coalesce(id,'x') = '1';

Index Scan using t_idx2 on t  (cost=0.29..8.30 rows=1 width=105)
  Index Cond: (COALESCE(id, 'x'::text) = '1'::text)

postgresql 视图中的合并未使用正确的联接顺序

1条答案

相关问题

热门标签

最新问答