On 9/13/21 3:24 PM, Kristjan Mustkivi wrote: > Dear community, > > I have a query that most of the time gets executed in a few > milliseconds yet occasionally takes ~20+ seconds. The difference, as > far as I am able to tell, comes whether it uses the table Primary Key > (fast) or an additional index with smaller size. The table in question > is INSERT ONLY - no updates or deletes done there. >
It'd be really useful to have explain analyze for the slow execution. My guess is there's a poor estimate, affecting some of the parameter values, and it probably resolves itself after autoanalyze run. I see you mentioned SET STATISTICS, so you tried increasing the statistics target for some of the columns? Have you tried lowering autovacuum_analyze_scale_factor to make autoanalyze more frequent? It's also possible most values are independent, but some values have a rather strong dependency, skewing the estimates. The MCV would help with that, but those are in PG12 :-( regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
