Re: Disk wait problem... may not be hardware... - Mailing list pgsql-general

From pf@pfortin.com
Subject Re: Disk wait problem... may not be hardware...
Date
Msg-id 20231029112420.6dfa6913.pfortin@pfortin.com
Whole thread Raw
In response to Re: Disk wait problem... may not be hardware...  ("Peter J. Holzer" <hjp-pgsql@hjp.at>)
List pgsql-general
On Sun, 29 Oct 2023 16:00:46 +0100 Peter J. Holzer wrote:

>On 2023-10-27 19:46:09 -0400, pf@pfortin.com 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



pgsql-general by date:

Previous
From: "Peter J. Holzer"
Date:
Subject: Re: [SOLVED?] Re: Disk wait problem... not hardware...
Next
From: Ron
Date:
Subject: Re: pg_checksums?