PostgreSQL – Query Planner Not Respecting Table Inheritance Constraint for NULL

execution-planoptimizationpostgresqlpostgresql-9.3

I'm having an issue with slow queries that is caused by the planner checking every inherited table instead of only the one with the constraint.

I have a table with 0 rows called "search_result". This table has a few inherited tables with different constraints based on the "polarization", where all our data resides. For example: 

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);

When I execute a query using "WHERE polarization = 1", for example, the query planner will show that it has only checked the "search_result_positive": table, which is the desired behavior.

However, when the query is "WHERE polarization IS NULL", it'll check every single table, taking an immense amount of time. Here's an example for "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

And now, this is what happens when we query for 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

This is driving me crazy. I don't understand why every table is being checked instead of only "search_result_unpolarized". This is driving me crazy already. The only other choice I have for this is to rewrite part of our system to query only a specific table instead of letting Postgres decide, which will be a fair amount of work. There must be a better way. Any help or ideas would be greatly appreciated.

We're using Postgres 9.3.19 on Amazon RDS

Best Answer

Constraint exclusion has some caveats. This very case (that non-NULL-ness is not considered, even when the existing check implicates it) is not listed in the documentation, but it's still there in later versions, too. The solution is to add another check that explicitly excludes NULLs:

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