On Sun, 29 Oct 2023 16:00:46 +0100 Peter J. Holzer wrote: >On 2023-10-27 19:46:09 -0400, pf@xxxxxxxxxxx wrote: >> On Fri, 27 Oct 2023 19:07:11 +0200 Peter J. Holzer wrote: >> >Have you looked at the query plans as I recommended? (You might also >> >want to enable track_io_timing to get extra information, but comparing >> >just the query plans of fast and slow queries would be a first step) >> >> I didn't see how that would help since other than the table name the >> queries are identical. Curious: are you implying PG stores tables >> differently? > >No, but Postgres decides on the query depending on the statistics stored >about that table. If those statistics are off, the query plan can be >wildly different and very inefficient. So checking whether the plans are >plausible should be one of the first things you do when performance is >not what you expect. Indeed, on >https://wiki.postgresql.org/wiki/Slow_Query_Questions it is the very >first bullet point in the section "Things to Try Before You Post". > >When you have established that the plan looks fine even when the >performance is poor then you have to look elsewhere. But even then it >helps to know what the database is actually trying to do. > > hp Peter, Thanks for this and the link... Thanks to Jim's post, I came to a similar conclusion; but the above link could use a bit more emphasis on shared_buffers. I'm no longer enabling "autoload table row count" and pointing out to our team that SELECT count(*) FROM table; is OK *unless* done in a loop across many tables where shared-buffers may never be enough to prevent thrashing... Thanks, Pierre
