From: Tom Lane <tgl@sss.pgh.pa.us>
To: MirrorX <mirrorx@gmail.com>
cc: pgsql-admin@postgresql.org
Subject: Re: vacuum and query plans?
In-reply-to: <1536759109470-0.post@n3.nabble.com>
References: <1536759109470-0.post@n3.nabble.com>
Comments: In-reply-to MirrorX <mirrorx@gmail.com>
	message dated "Wed, 12 Sep 2018 06:31:49 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2417.1536762527.1@sss.pgh.pa.us>
Date: Wed, 12 Sep 2018 10:28:47 -0400
Message-ID: <2418.1536762527@sss.pgh.pa.us>
Precedence: bulk

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