Do table-level CHECK constraints affect the query optimizer?

From: Ron <ronljohnsonjr(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Do table-level CHECK constraints affect the query optimizer?
Date: 2021-06-29 15:10:21
Message-ID: 5f718120-a286-71ca-3d31-7bad70f0194e@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Postgresql 12.5

I've got a big (about 50M rows, but 1.4TB because of xml attachments)
partitioned table full of data that we're seeing sequential scans on, even
though there are supporting indices. Will adding CHECK constraints on the
children, which match the partition ranges influence the query optimizer?

(We'd try this on the test system -- which has just a few weeks less data
than prod -- but queries on it use the proper indices, and thus perform as
expected.  Both "test" and "prod" have identical IO systems, and
postgresql.conf files.)

Partitions: request_xml_p2015_07 FOR VALUES FROM ('2015-07-01 00:00:00') TO
('2015-08-01 00:00:00'),
            request_xml_p2015_08 FOR VALUES FROM ('2015-08-01 00:00:00') TO
('2015-09-01 00:00:00'),
            request_xml_p2015_09 FOR VALUES FROM ('2015-09-01 00:00:00') TO
('2015-10-01 00:00:00'),
            request_xml_p2015_10 FOR VALUES FROM ('2015-10-01 00:00:00') TO
('2015-11-01 00:00:00'),
[snip]
            request_xml_p2021_06 FOR VALUES FROM ('2021-06-01 00:00:00') TO
('2021-07-01 00:00:00'),
            request_xml_p2021_07 FOR VALUES FROM ('2021-07-01 00:00:00') TO
('2021-08-01 00:00:00'),
            request_xml_p2021_08 FOR VALUES FROM ('2021-08-01 00:00:00') TO
('2021-09-01 00:00:00')

--
Angular momentum makes the world go 'round.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Lewis 2021-06-29 15:41:00 Re: Do table-level CHECK constraints affect the query optimizer?
Previous Message Ron 2021-06-29 03:53:09 CREATE INDEX ONLY on a partitioned table?