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

Поиск
Список
Период
Сортировка
От Ron
Тема Re: Do table-level CHECK constraints affect the query optimizer?
Дата
Msg-id 374d530d-ec98-f1ea-8d41-21e661aa9ab4@gmail.com
обсуждение исходный текст
Ответ на Re: Do table-level CHECK constraints affect the query optimizer?  (Michael Lewis <mlewis@entrata.com>)
Ответы Re: Do table-level CHECK constraints affect the query optimizer?  (Michael Lewis <mlewis@entrata.com>)
Re: Do table-level CHECK constraints affect the query optimizer?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Michael Lewis
Дата:
Сообщение: Re: Do table-level CHECK constraints affect the query optimizer?
Следующее
От: Michael Lewis
Дата:
Сообщение: Re: Do table-level CHECK constraints affect the query optimizer?