Re: clear cache in postgresql - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: clear cache in postgresql
Date
Msg-id 20210616110310.GB26107@hjp.at
Whole thread Raw
In response to Re: clear cache in postgresql  (Julien Rouhaud <rjuju123@gmail.com>)
List pgsql-general
On 2021-06-16 14:39:19 +0800, Julien Rouhaud wrote:
> On Wed, Jun 16, 2021 at 12:02:52PM +0530, Atul Kumar wrote:
> > Sometimes I run a Postgres query it takes 30 seconds. Then, I
> > immediately run the same query and it takes 2 seconds.
[...]
> > Can I force all caches to be cleared for tuning purposes?
> > So I need to clear the cache without restarting/rebooting the postgres
> > server to check the correct execution plan of my query.
>
> No, cleaning postgres cache can only be done with a service restart.  That
> being said, tuning shouldn't be done assuming that there's no cache.

I agree mostly, but not entirely. The most important case to optimize is
of course the normal case, where at least some of the data will already
be cached. Hoewever, I do think it is also important to ensure that the
rare cases are still acceptable. If a given operation takes 2 seconds
95 % of the time but 30 seconds 5 % of the time that makes for a poor
user experience, expecially if its seemingly at random (because it
depends on what other users have done recently). So you may want to
investigate those cases, too.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

pgsql-general by date:

Previous
From: David Rowley
Date:
Subject: Re: query issue
Next
From: Marcin Barczynski
Date:
Subject: Establishing a local connection to PostgreSQL 9.6 takes seconds