From: Tom Lane <tgl@sss.pgh.pa.us>
To: MirrorX <mirrorx@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: vacuum and query plans?
Date: Wed, 12 Sep 2018 10:28:47 -0400
Message-ID: <2418.1536762527@sss.pgh.pa.us> (raw)
In-Reply-To: <1536759109470-0.post@n3.nabble.com>
References: <1536759109470-0.post@n3.nabble.com>
MirrorX <mirrorx@gmail.com> writes:
> i ve seen lately more and more occurences of queries having huge planning
> times (>500ms) while their execution time is very low (<10ms). at that
> point, if a vacuum is executed on the table involved (without analyze) then
> the planning time goes back to 'normal'. could someone elaborate on the
> connection between planning time and what vacuum does? i am asking bc i
> would have thought that an analyze would have resolved the issue (with
> increased sampling for example) but it didnt. only vacuum does resolve it.
> (the version is 9.4.x)
I'm leaping to a conclusion from insufficient data here, but: what this
sounds like is a known issue where the planner spends too much time trying
to identify the current minimum or maximum value of a column by searching
an index for that column. In principle that should be quick, but if there
are a whole lot of recently-dead entries at the relevant end of the index,
it's not quick. VACUUM fixes it by removing said entries.
We've gone through a couple rounds of refinement to improve that
situation, but the most recent one is only in v11 not prior branches:
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3ca930fc3
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, mirrorx@gmail.com
Subject: Re: vacuum and query plans?
In-Reply-To: <2418.1536762527@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox