On Mon, 2024-02-26 at 18:25 -0600, Chema wrote: > 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. > > Reading around, seems many people are still using this 2005 snippet to obtain > the > row count estimate from Explain:
I recommend using FORMAT JSON and extracting the top row count from that. It is simpler and less error-prone. > Is this still the current best practice? Any tips to increase precision? > Currently it can estimate the actual number of rows for over or under a > million, > as seen on the sample queries (1,955,297 instead of 1,001,200; 162,080 instead > of 1,292,010). Looking at the samples you provided, I get the impression that the statistics for the table are quite outdated. That will affect the estimates. Try running ANALYZE and see if that improves the estimates. Yours, Laurenz Albe
