Re: Do table-level CHECK constraints affect the query optimizer? - Mailing list pgsql-general

From Tom Lane
Subject Re: Do table-level CHECK constraints affect the query optimizer?
Date
Msg-id 449566.1624984976@sss.pgh.pa.us
Whole thread Raw
In response to Re: Do table-level CHECK constraints affect the query optimizer?  (Ron <ronljohnsonjr@gmail.com>)
Responses Re: Do table-level CHECK constraints affect the query optimizer?
List pgsql-general
Ron <ronljohnsonjr@gmail.com> writes:
> On 6/29/21 10:41 AM, Michael Lewis wrote:
>> What's an example query that uses indexes on test and does not on live?

> SELECT COUNT(*) FROM sep_info_report_extract;

> On prod, there's a list of "Parallel Seq Scan on xxxx_partname" records in
> the EXPLAIN output, while the test system has a list of "Parallel Index Only
> Scan using ..._idx" records.

It'd be worth checking pg_class.relallvisible page counts for the
partitions on both systems.  If an IOS is possible, the main thing
that might push the planner to do a seqscan instead is if it thinks
that too little of the table is all-visible, which would tend to
inflate the index-only scan towards the same cost as a regular index
scan (which'll almost always be considered slower than seqscan).

If there's a significant difference in relallvisible fractions, that
would point to something different in your VACUUM housekeeping on
the two systems.

            regards, tom lane



pgsql-general by date:

Previous
From: Michael Lewis
Date:
Subject: Re: Do table-level CHECK constraints affect the query optimizer?
Next
From: Ron
Date:
Subject: Re: Do table-level CHECK constraints affect the query optimizer?