主页 / dba / 问题 / 179495
Accepted
Ian Timothy
Asked: 2017-07-11 10:00:35 +0800 CST

在 WHERE 子句中使用 NULLIF() 更改计划器

  • 772

在 9.6.3 中，在 WHERE 子句中添加 NULLIF() 会将计划从位图堆扫描更改为索引扫描，并将执行时间从大约 0.3 毫秒更改为 43,000 毫秒。

这是一个相关的问题：https ://stackoverflow.com/questions/21062148/how-to-query-postgres-on-optional-params

这是快速查询和计划：

SELECT
        c.name_first, c.name_last, c.postal_code,
        v.tag as plate_number, v.tag_state as plate_state, v.year, v.make, v.model
FROM customers_vehicles AS v
        JOIN customers AS c ON (v.customer_id = c.id)
WHERE ('XXXXXX'::text is null OR v.tag='XXXXXX');
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                  QUERY PLAN                                                                   │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop  (cost=824.67..451863.32 rows=43830 width=228) (actual time=0.202..0.203 rows=1 loops=1)                                          │
│   ->  Bitmap Heap Scan on customers_vehicles v  (cost=824.12..121946.77 rows=43830 width=164) (actual time=0.104..0.104 rows=1 loops=1)       │
│         Recheck Cond: (tag = 'XXXXXX'::text)                                                                                                  │
│         Heap Blocks: exact=1                                                                                                                  │
│         ->  Bitmap Index Scan on customers_vehicles_tag_idx  (cost=0.00..813.16 rows=43830 width=0) (actual time=0.090..0.090 rows=1 loops=1) │
│               Index Cond: (tag = 'XXXXXX'::text)                                                                                              │
│   ->  Index Scan using customers_pkey on customers c  (cost=0.56..7.52 rows=1 width=128) (actual time=0.093..0.094 rows=1 loops=1)            │
│         Index Cond: (id = v.customer_id)                                                                                                      │
│ Planning time: 0.268 ms                                                                                                                       │
│ Execution time: 0.256 ms                                                                                                                      │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

这是缓慢的查询和计划：

SELECT
        c.name_first, c.name_last, c.postal_code,
        v.tag as plate_number, v.tag_state AS plate_state, v.year, v.make, v.model
FROM customers_vehicles AS v
        JOIN customers as c on (v.customer_id = c.id)
WHERE (nullif('XXXXXX'::text,'') IS NULL OR v.tag='XXXXXX');
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                    QUERY PLAN                                                                                    │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop  (cost=1.12..7509681.67 rows=87441 width=228) (actual time=5462.111..42209.513 rows=1 loops=1)                                                                       │
│   ->  Index Scan using customers_vehicles_customer_id_idx on customers_vehicles v  (cost=0.56..6921180.68 rows=87441 width=164) (actual time=5462.023..42209.423 rows=1 loops=1) │
│         Filter: ((NULLIF('XXXXXX'::text, ''::text) IS NULL) OR (tag = 'XXXXXX'::text))                                                                                             │
│         Rows Removed by Filter: 8766005                                                                                                                                          │
│   ->  Index Scan using customers_pkey on customers c  (cost=0.56..6.72 rows=1 width=128) (actual time=0.080..0.081 rows=1 loops=1)                                               │
│         Index Cond: (id = v.customer_id)                                                                                                                                         │
│ Planning time: 0.209 ms                                                                                                                                                          │
│ Execution time: 42209.549 ms                                                                                                                                                     │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

唯一的区别在于 WHERE 子句中的慢查询：'XXXXXX' IS NULL更改为nullif('XXXXXX','') IS NULL. 如果第一个条件包含 ，规划器似乎无法优化它NULLIF()。有没有办法强迫它这样做？

postgresql

1 个回答

  1. Best Answer

    尝试将谓词更改为以下内容：

    WHERE ('XXXXXX'::text is null OR v.tag='XXXXXX' OR 'XXXXXX' = '');

    因为从逻辑的角度来看，这实际上是该NULLIF语句正在做的事情。我假设'XXXXXX'在这种情况下是一列，而不仅仅是一个随机字符串，X对吗？

    • 0

相关问题