Postgres: query planner not respecting table inheritance constraint when querying for null

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_positiv

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

StackExchange Database Administrators Q#211477, answer score: 2