sql - Postgres View 条件下推不适用于加入

Question

我观察到奇怪的 postgres 行为并坚持使用正确的查询优化。

结构和测试数据:

CREATE table t_base(
    id serial PRIMARY KEY,
    value text
);    

SELECT *
FROM t_base;

CREATE TABLE t1 (
    id serial PRIMARY KEY,
    base_id int REFERENCES t_base(id),
    value text
);

CREATE TABLE t2 (
    id serial PRIMARY KEY,
    base_id int REFERENCES t_base(id),
    value text
);

CREATE VIEW v_all AS
    SELECT
        id, base_id, value, 't1' as tname
    FROM t1
    UNION ALL
    SELECT
        id, base_id, value, 't2' as tname
    FROM t2;

CREATE TABLE t_data (
    tname text,
    t_id int
);

INSERT INTO t_base (value)
SELECT 'val' || i FROM generate_series(1, 100000) s(i);

INSERT INTO t1 (base_id, value)
SELECT i, 't1_val' || i FROM generate_series(1, 50000) s(i);

INSERT INTO t2 (base_id, value)
SELECT i, 't2_val' || i FROM generate_series(50001, 100000) s(i);

INSERT INTO t_data VALUES ('t1', 1), ('t1', 4);

INSERT INTO t_data
SELECT 't1', (random()*100)::int
FROM generate_series(1, 3000) s(i);

VACUUM ANALYZE VERBOSE t_base;
VACUUM ANALYZE VERBOSE t1;
VACUUM ANALYZE VERBOSE t2;
VACUUM ANALYZE VERBOSE t_data;

View v_all 在这种情况下被简化了，实际上我有 9 个表，其中大部分有很多行。

现在我尝试查询它:

EXPLAIN ANALYZE
SELECT *
FROM v_all
WHERE tname = 't1' and id = 2;

QUERY PLAN                                                                                                        
----------------------------------- 
 Append  (cost=0.29..8.31 rows=1 width=51) (actual time=0.056..0.058 rows=1 loops=1)                               
   ->  Index Scan using t1_pkey on t1  (cost=0.29..8.31 rows=1 width=51) (actual time=0.055..0.056 rows=1 loops=1) 
         Index Cond: (id = 2)                                                                                      
 Planning time: 0.264 ms                                                                                           
 Execution time: 0.103 ms

太棒了!正是我想要的:

1. 只扫描表t1
2. 使用的索引

现在我想用 join 完成同样的事情:

EXPLAIN ANALYZE
SELECT *
FROM t_data d
    JOIN v_all v ON (v.tname = d.tname AND v.id = d.t_id);

 QUERY PLAN                                                                                                                 
 ------------------------------------------------------------------
 Nested Loop  (cost=0.29..3427.50 rows=3840 width=58) (actual time=0.058..15.649 rows=2986 loops=1)                         
   ->  Seq Scan on t_data d  (cost=0.00..44.00 rows=3000 width=7) (actual time=0.023..0.727 rows=3000 loops=1)              
   ->  Append  (cost=0.29..1.11 rows=2 width=51) (actual time=0.003..0.004 rows=1 loops=3000)                               
         ->  Index Scan using t1_pkey on t1  (cost=0.29..0.92 rows=1 width=51) (actual time=0.002..0.003 rows=1 loops=3000) 
               Index Cond: (id = d.t_id)                                                                                    
               Filter: (d.tname = 't1'::text)                                                                               
         ->  Index Scan using t2_pkey on t2  (cost=0.15..0.19 rows=1 width=72) (actual time=0.001..0.001 rows=0 loops=3000) 
               Index Cond: (id = d.t_id)                                                                                    
               Filter: (d.tname = 't2'::text)                                                                               
 Planning time: 0.626 ms                                                                                                    
 Execution time: 16.095 ms 

结果我不希望同时扫描 t1 和 t2 表以按 ID 进行搜索，而不是通过常量跳过那个“分支”而不是呈现为 过滤器:(d.tname = 't1'::text)

我已经尝试过 postgres 10.3 和 9.4:

select version();
PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

是否有一些技术可以教 postgres 不使用 UNION ALL 子句查看表，只需首先在 JOINs< 上检查常量条件?

SqlFiddle

Answer 1

请记住 PostgreSQL 在执行 5 次后“固定”执行计划。

这意味着，如果在第五次执行后它出错或不完整，它不会在之后修复它——直到您重新初始化它。

• 在第一个示例中，PostgreSQL 避免使用第二个表，因为您使用了常量值。它需要另一张 table 的可能性为零。 PostgreSQL 可以立即针对这种情况确定计划，因为根本不需要扫描其他表。

• 但是，在第二种情况下，PostgreSQL 无法固定计划并永远排除第二个表。现在删除它可能是合理的，但明天或下周呢？ PostgreSQL 不能冒险确定一个适合当前值的执行计划……这在未来可能是错误的。这就是为什么它“保持其选择的开放性”，用简单的英语来说。

反正这样的方案也不贵。这只是一个索引扫描，将立即(今天)执行并结束。如果下周您为“t2”添加值，那么它会很有意义，并且会花费必要的时间来适本地执行。