pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christophe Pettus <xof@thebuild.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Dimitrios Apostolou <jimis@gmx.net>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)
Date: Wed, 01 Feb 2023 13:56:02 -0500
Message-ID: <1402526.1675277762@sss.pgh.pa.us> (raw)
In-Reply-To: <01D1B408-EADC-41C7-A695-7C84F649F702@thebuild.com>
References: <b847d458-5a03-718e-5467-422fc0e51004@gmx.net>
	<0c18ab4b-b8fd-ac03-2118-066fe0362246@gmx.net>
	<CAKFQuwYnx==0Ze_DUocUWN-sFitOXCpYH9RL14iV_BkczAjyCg@mail.gmail.com>
	<01D1B408-EADC-41C7-A695-7C84F649F702@thebuild.com>

Christophe Pettus <xof@thebuild.com> writes:
>> On Feb 1, 2023, at 10:45, David G. Johnston <david.g.johnston@gmail.com> wrote:
>> The system just isn't that intelligent for "sequential scan", instead it does literally what the label says, goes through the table one page at a time and returns any live rows it finds. 

> Although this does raise a question: Couldn't it skip pages that are marked appropriately in the visibility map?

There's no bit in the visibility map for "contains no live tuples".
I think adding one would be optimizing for the wrong thing.

(You might be thinking of the free-space map, but we don't maintain
that rigorously enough to trust it as a guide to skipping pages.)

			regards, tom lane





view thread (4+ messages)

Message-ID: <1402526.1675277762@sss.pgh.pa.us>
Permalink:  ../1402526.1675277762@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1402526.1675277762@sss.pgh.pa.us

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, xof@thebuild.com, david.g.johnston@gmail.com, jimis@gmx.net
  Subject: Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)
  In-Reply-To: <1402526.1675277762@sss.pgh.pa.us>
  References: <b847d458-5a03-718e-5467-422fc0e51004@gmx.net> <0c18ab4b-b8fd-ac03-2118-066fe0362246@gmx.net> <CAKFQuwYnx==0Ze_DUocUWN-sFitOXCpYH9RL14iV_BkczAjyCg@mail.gmail.com> <01D1B408-EADC-41C7-A695-7C84F649F702@thebuild.com> <1402526.1675277762@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