Re: Do table-level CHECK constraints affect the query optimizer?

On 6/29/21 11:42 AM, Tom Lane wrote:
> Ron <ronljohnsonjr(at)gmail(dot)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.

Lots of 0 records in prod, and lots of "numbers" in test.

> 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.

Prod is brand new.  Loaded on Saturday; we saw this problem on Sunday during
pre-acceptance.  Thus, while running ANALYZE was top of the list of Things
To Do, running VACUUM was low.

Is that a mistaken belief?

--
Angular momentum makes the world go 'round.