Hi Chema, On 2024-Feb-26, Chema wrote: > Dear pgsqlers, > > I'm trying to optimize simple queries on two tables (tenders & items) with > a couple million records. Besides the resulting records, the app also > displays the count of total results. Doing count() takes as much time as > the other query (which can be 30+ secs), so it's an obvious target for > optimization. I'm already caching count() results for the most common > conditions (country & year) in a material table, which practically halves > response time. The tables are updated sparingly, and only with bulk > COPYs. Now I'm looking for ways to optimize queries with other conditions. It sounds like this approach might serve your purposes: https://www.postgresql.eu/events/pgconfeu2023/schedule/session/4762-counting-things-at-the-speed-of-light-with-roaring-bitmaps/ > I already raised default_statistics_target up to 2k (the planner wasn't > using indexes at all with low values). Gotta get it even higher? These are > my custom settings: I would recommend to put the default_statistics_target back to its original value and modify the value with ALTER TABLE .. SET STATISTICS only for columns that need it, only on tables that need it; then ANALYZE everything. The planner gets too slow if you have too many stats for everything. > shared_buffers = 256MB # min 128kB This sounds far too low, unless your server is a Raspberry Pi or something. See "explain (buffers, analyze)" of your queries to see how much buffer traffic is happening for them. > Functions: 33 > Options: Inlining true, Optimization true, Expressions true, Deforming true > Timing: Generation 14.675 ms, Inlining 383.349 ms, Optimization 1023.521 > ms, Emission 651.442 ms, Total 2072.987 ms > Execution Time: 63378.033 ms Also maybe experiment with turning JIT off. Sometimes it brings no benefit and slows down execution pointlessly. Here you spent two seconds JIT-compiling the query; were they worth it? Cheers -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ Syntax error: function hell() needs an argument. Please choose what hell you want to involve.
