From: | pf(at)pfortin(dot)com |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Disk wait problem... may not be hardware... |
Date: | 2023-10-29 15:24:20 |
Message-ID: | 20231029112420.6dfa6913.pfortin@pfortin.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, 29 Oct 2023 16:00:46 +0100 Peter J. Holzer wrote:
>On 2023-10-27 19:46:09 -0400, pf(at)pfortin(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2023-10-29 15:37:19 | Re: pg_checksums? |
Previous Message | Peter J. Holzer | 2023-10-29 15:16:05 | Re: [SOLVED?] Re: Disk wait problem... not hardware... |