From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Do table-level CHECK constraints affect the query optimizer? |
Date: | 2021-06-29 16:13:03 |
Message-ID: | 374d530d-ec98-f1ea-8d41-21e661aa9ab4@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 6/29/21 10:41 AM, Michael Lewis wrote:
> Are vacuum and analyze happening regularly on the live system?
Yes. There's a nightly cron job which vacuums those it thinks needs it
(though it's INSERT-heavy), and ditto ANALYZE.
Specifically, I ran ANALYZE on the prod table just before running the query.
Also, the sampling rate on all tables in both prod and test is 60000 rows.
> 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.
(Yes, this is a simple COUNT(*) but it's *a simple reproducer*, which
*demonstrates* the kind of problems we're having on *much more complex
queries*.)
> Does the live system show poor estimates when executing 'explain analyze
> select...' and the test system show semi-accurate row estimates?
They're within 2% of each other.
> 50 million seems to be a fairly low row count to be partitioned. What
> version is this on?
As explained in the OP, each record has a (sometimes large) XML record;
months can have up to 240GB. Besides, partitioning makes for easy archiving.
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2021-06-29 16:21:54 | Re: Do table-level CHECK constraints affect the query optimizer? |
Previous Message | Michael Lewis | 2021-06-29 15:41:00 | Re: Do table-level CHECK constraints affect the query optimizer? |