From: Justin Pryzby <pryzby@telsasoft.com>
To: Jiří Fejfar <jurafejfar@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: performance of analytical query
Date: Tue, 23 Nov 2021 13:42:35 -0600
Message-ID: <20211123194234.GV17618@telsasoft.com> (raw)
In-Reply-To: <CA+8wVNWevbBwsNp-tkpF-BjUytwBSxRr1EnkU2-+ncdmRy__Bg@mail.gmail.com>
References: <CA+8wVNV98Z-RDM4R+gm-ds17LVcOODc-NB1Ov9dTFQjuHa+1_Q@mail.gmail.com>
<20211112024151.GF17618@telsasoft.com>
<CA+8wVNWevbBwsNp-tkpF-BjUytwBSxRr1EnkU2-+ncdmRy__Bg@mail.gmail.com>
On Fri, Nov 12, 2021 at 09:12:38PM +0100, Jiří Fejfar wrote:
> * I know that PG is focused on OLTP rather then analytics, but we are happy
> with it at all and do not wish to use another engine for analytical
> queries... isn't somewhere some "PG analytical best practice" available?
It's a good question. Here's some ideas:
I don't think we know what version you're using - that's important, and there's
other ideas here:
https://wiki.postgresql.org/wiki/Slow_Query_Questions
You said that your query was slow "probably after VACUUM ANALYZE".
Is it really faster without stats ? You can do this to see if there was really
a better plan "before":
| begin; DELETE FROM pg_statistic WHERE starelid='thetable'::regclass; explain analyze ...; rollback;
Try enable_nestloop=off for analytic queries;
Test whether jit=off helps you or hurts you (you said that it's already disabled);
You can do other things that can improve estimates, by sacrificing planning time
(which for an analytic query is a small component of the total query time, and
pays off at runtime if you can get a btter plan):
- FKs can help with estimates since pg9.6;
- CREATE STATISTICS;
- ALTER SET STATISTICS or increase default_statistics_target;
- increase from_collapse_limit and join_collapse_limit. But I don't think it
will help your current query plan.
- partitioning data increases planning time, and (if done well) can allow
improved execution plans;
You can REINDEX or maybe CLUSTER during "off hours" to optimize indexes/tables.
BRIN indexes (WITH autoanalyze) are very successful for us, here.
You can monitor your slow queries using auto_explain and/or pg_stat_statements.
You can reduce autovacuum_analyze_threshold to analyze more often.
I'd be interested to hear if others have more suggestions.
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, jurafejfar@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: performance of analytical query
In-Reply-To: <20211123194234.GV17618@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox