pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
vacuum and query plans? 3+ messages / 2 participants [nested] [flat]
* vacuum and query plans? @ 2018-09-12 13:31 MirrorX <mirrorx@gmail.com> 2018-09-12 14:28 ` Re: vacuum and query plans? Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 3+ messages in thread From: MirrorX @ 2018-09-12 13:31 UTC (permalink / raw) To: pgsql-admin hello, a bit of a theoretical question if you dont mind.. 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. thank you in advance (the version is 9.4.x) -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: vacuum and query plans? 2018-09-12 13:31 vacuum and query plans? MirrorX <mirrorx@gmail.com> @ 2018-09-12 14:28 ` Tom Lane <tgl@sss.pgh.pa.us> 2018-09-12 14:47 ` Re: vacuum and query plans? MirrorX <mirrorx@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2018-09-12 14:28 UTC (permalink / raw) To: MirrorX <mirrorx@gmail.com>; +Cc: pgsql-admin 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: vacuum and query plans? 2018-09-12 13:31 vacuum and query plans? MirrorX <mirrorx@gmail.com> 2018-09-12 14:28 ` Re: vacuum and query plans? Tom Lane <tgl@sss.pgh.pa.us> @ 2018-09-12 14:47 ` MirrorX <mirrorx@gmail.com> 0 siblings, 0 replies; 3+ messages in thread From: MirrorX @ 2018-09-12 14:47 UTC (permalink / raw) To: pgsql-admin makes sense. thank you very much for clarifying it -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-09-12 14:47 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-09-12 13:31 vacuum and query plans? MirrorX <mirrorx@gmail.com> 2018-09-12 14:28 ` Tom Lane <tgl@sss.pgh.pa.us> 2018-09-12 14:47 ` MirrorX <mirrorx@gmail.com>
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