From: | Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-committers(at)postgresql(dot)org |
Subject: | Re: pgsql: Measure the number of all-visible pages for use in index-only sc |
Date: | 2011-10-15 14:03:48 |
Message-ID: | CAF6yO=3LTSdkn1xCAhGw3HUvA_JVfwAT6YB0mPrqOJ_6Ncz8vA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-committers |
2011/10/14 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Measure the number of all-visible pages for use in index-only scan costing.
>
> Add a column pg_class.relallvisible to remember the number of pages that
> were all-visible according to the visibility map as of the last VACUUM
> (or ANALYZE, or some other operations that update pg_class.relpages).
> Use relallvisible/relpages, instead of an arbitrary constant, to estimate
> how many heap page fetches can be avoided during an index-only scan.
>
> This is pretty primitive and will no doubt see refinements once we've
> acquired more field experience with the index-only scan mechanism, but
> it's way better than using a constant.
Fun! it is exactly what I proposed to handle cache estimate and to be
able to refine the costsize based on that. (idea is a (1.0 - var) in
first stage, like you did).
Comments ( http://archives.postgresql.org/message-id/BANLkTikO-M0eBqg0AXynZ+Dq0gdNWUvcbQ@mail.gmail.com
) are valid here, except point 3. (and maybe not so clear that we
*need* a new column to store visibility estimate, we may just use a
mecanism like autovacuum)
>
> Note: I had to adjust an underspecified query in the window.sql regression
> test, because it was changing answers when the plan changed to use an
> index-only scan. Some of the adjacent tests perhaps should be adjusted
> as well, but I didn't do that here.
>
> Branch
> ------
> master
>
> Details
> -------
> http://git.postgresql.org/pg/commitdiff/e6858e665731c0f56d3ecc9fbb245c32d24f8ef7
>
> Modified Files
> --------------
> doc/src/sgml/catalogs.sgml | 13 ++++++
> src/backend/access/hash/hash.c | 3 +-
> src/backend/access/heap/visibilitymap.c | 68 +++++++++++++++++++++++++++++++
> src/backend/catalog/heap.c | 4 ++
> src/backend/catalog/index.c | 52 ++++++++++++++++-------
> src/backend/commands/analyze.c | 11 ++++-
> src/backend/commands/cluster.c | 5 ++
> src/backend/commands/vacuum.c | 6 +++
> src/backend/commands/vacuumlazy.c | 20 ++++++++-
> src/backend/nodes/outfuncs.c | 1 +
> src/backend/optimizer/path/costsize.c | 18 ++++-----
> src/backend/optimizer/util/plancat.c | 30 ++++++++++++-
> src/backend/optimizer/util/relnode.c | 2 +
> src/backend/utils/cache/relcache.c | 2 +
> src/include/access/visibilitymap.h | 3 +-
> src/include/catalog/catversion.h | 2 +-
> src/include/catalog/pg_class.h | 45 +++++++++++---------
> src/include/commands/vacuum.h | 1 +
> src/include/nodes/relation.h | 4 +-
> src/include/optimizer/plancat.h | 2 +-
> src/test/regress/expected/window.out | 23 +++++-----
> src/test/regress/sql/window.sql | 3 +-
> 22 files changed, 246 insertions(+), 72 deletions(-)
>
>
> --
> Sent via pgsql-committers mailing list (pgsql-committers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-committers
>
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2011-10-15 14:09:10 | pgsql: Improve doc wording of drop table permission. |
Previous Message | User Uwebartels | 2011-10-15 10:50:00 | pgstatspack - pgstatspack: |