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
