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.