Postgres:查询计划器在查询null时不遵守表继承约束

Question

由于计划器检查每个继承的表，而不是只检查带有约束的表，我遇到了一个缓慢的查询问题。

我有一个0行的表，名为"search_result“。这个表有几个基于“极化”的不同约束的继承表，我们所有的数据都在这里。例如：

CREATE TABLE search_result_positive
(
  CONSTRAINT search_result_positive_polarization_check CHECK (polarization = (1))
)
INHERITS (search_result);

CREATE TABLE search_result_negative
(
  CONSTRAINT search_result_negative_polarization_check CHECK (polarization = (-1))
)
INHERITS (search_result);

CREATE TABLE search_result_unpolarized
(
  CONSTRAINT search_result_unpolarized_polarization_check CHECK (polarization IS NULL)
)
INHERITS (search_result);

例如，当我使用“WHERPO极化= 1”执行查询时，查询规划器将显示它只检查了"search_result_positive"：表，这是所需的行为。

但是，当查询是“极化为空”时，它将检查每个表，花费大量时间。下面是“search_result_positive”的一个示例：

SELECT  "search_result".* FROM "search_result"  WHERE (polarization = 1)  ORDER BY published_on DESC LIMIT 20;

Limit  (cost=0.44..17.65 rows=20 width=2027) (actual time=3.638..3.666 rows=20 loops=1)
  ->  Merge Append  (cost=0.44..249453.67 rows=289872 width=2027) (actual time=3.637..3.663 rows=20 loops=1)
        Sort Key: search_result.published_on DESC
        ->  Sort  (cost=0.01..0.02 rows=1 width=2882) (actual time=0.004..0.004 rows=0 loops=1)
              Sort Key: search_result.published_on DESC
              Sort Method: quicksort  Memory: 25kB
              ->  Seq Scan on search_result  (cost=0.00..0.00 rows=1 width=2882) (actual time=0.002..0.002 rows=0 loops=1)
                    Filter: (polarization = 1)
        ->  Index Scan Backward using ix_search_result_positive_published_on_asc on search_result_positive  (cost=0.42..245830.25 rows=289871 width=2027) (actual time=3.633..3.658 rows=20 loops=1)
              Filter: (polarization = 1)
Planning time: 2.640 ms
Execution time: 3.762 ms

现在，当我们查询null时会发生这样的情况：

SELECT  "search_result".* FROM "search_result"  WHERE (polarization IS NULL)  ORDER BY published_on DESC LIMIT 20;

Limit  (cost=1.93..143.83 rows=20 width=1758) (actual time=205790.210..206266.419 rows=20 loops=1)
  ->  Merge Append  (cost=1.93..462744.74 rows=65221 width=1758) (actual time=205790.208..206266.403 rows=20 loops=1)
        Sort Key: search_result.published_on DESC
        ->  Sort  (cost=0.01..0.02 rows=1 width=2882) (actual time=0.006..0.006 rows=0 loops=1)
              Sort Key: search_result.published_on DESC
              Sort Method: quicksort  Memory: 25kB
              ->  Seq Scan on search_result  (cost=0.00..0.00 rows=1 width=2882) (actual time=0.001..0.001 rows=0 loops=1)
                    Filter: (polarization IS NULL)
        ->  Index Scan using ix_search_result_hybrid_published_on_desc on search_result_hybrid  (cost=0.14..44.44 rows=1 width=2882) (actual time=0.002..0.002 rows=0 loops=1)
              Filter: (polarization IS NULL)
        ->  Index Scan Backward using ix_search_result_negative_published_on_asc on search_result_negative  (cost=0.42..126163.49 rows=1 width=2039) (actual time=44646.431..44646.431 rows=0 loops=1)
              Filter: (polarization IS NULL)
              Rows Removed by Filter: 174428
        ->  Index Scan Backward using ix_search_result_neutral_published_on_asc on search_result_neutral  (cost=0.42..53749.59 rows=1 width=1996) (actual time=29539.393..29539.393 rows=0 loops=1)
              Filter: (polarization IS NULL)
              Rows Removed by Filter: 115678
        ->  Index Scan using ix_search_result_no_apply_published_on_desc on search_result_no_apply  (cost=0.14..44.44 rows=1 width=2882) (actual time=0.003..0.003 rows=0 loops=1)
              Filter: (polarization IS NULL)
        ->  Index Scan Backward using ix_search_result_positive_published_on_asc on search_result_positive  (cost=0.42..245105.57 rows=1 width=2027) (actual time=131590.509..131590.509 rows=0 loops=1)
              Filter: (polarization IS NULL)
              Rows Removed by Filter: 295475
        ->  Index Scan using ix_search_result_unpolarized_published_on_desc on search_result_unpolarized  (cost=0.29..35643.06 rows=65215 width=1758) (actual time=13.863..490.048 rows=20 loops=1)
              Filter: (polarization IS NULL)
Planning time: 1.197 ms
Execution time: 206266.593 ms

快把我逼疯了。我不明白为什么要检查每个表，而不是只检查"search_result_unpolarized“。这已经把我逼疯了。对此，我唯一的选择是重写系统的一部分，只查询特定的表，而不是让Postgres决定，这将是相当大的工作量。一定有更好的办法。任何帮助或想法都将不胜感激。

我们在Amazon上使用Postgres 9.3.19

Answer 1

约束排除有一些警告。这种情况(不考虑非空性，即使现有的检查涉及到非空性)没有在文档中列出，但在以后的版本中仍然存在。解决方案是添加另一个显式排除NULL的检查：

ALTER TABLE search_result_positive ADD CHECK (polarization IS NOT NULL);
ALTER TABLE search_result_negative ADD CHECK (polarization IS NOT NULL);