Date: Tue, 23 Nov 2021 13:42:35 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: =?utf-8?B?SmnFmcOt?= Fejfar <jurafejfar@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: performance of analytical query
Message-ID: <20211123194234.GV17618@telsasoft.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>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CA+8wVNWevbBwsNp-tkpF-BjUytwBSxRr1EnkU2-+ncdmRy__Bg@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20211123194234.GV17618%40telsasoft.com>
Precedence: bulk

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