Skip site navigation (1) Skip section navigation (2)

Re: Measuring relation free space

From: Jaime Casanova <jaime(at)2ndquadrant(dot)com>
To: Noah Misch <noah(at)leadboat(dot)com>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Bernd Helmle <mailings(at)oopsware(dot)de>, Greg Smith <greg(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Measuring relation free space
Date: 2012-02-14 07:04:26
Message-ID: CAJKUy5jEP=uUGYHT54t5MH0q_n_vhxEqKLSDnZMOzz4534-Y4w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, Jan 25, 2012 at 9:47 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
>
> With all that done, run some quick benchmarks: see how "SELECT free_percent
> FROM pgstattuple(rel)" fares compared to "SELECT relation_free_space(rel)" for
> a large heap and for a large B-tree index.  If the timing difference is too
> small to be interesting to you, remove relation_free_space() and submit your
> pgstattuple() improvements alone.  Otherwise, submit as written.
>

Ok. I split this in three patches.

1) pgstattuple-gin_spgist.patch
This first patch adds gin and spgist support to pgstattuple, also
makes pgstattuple use a ring buffer when reading tables or indexes.

2) pgstattuple-relation_free_space.patch
This patch adds the relation_free_space function to pgstattuple.

the function relation_free_space() is faster than pgstattuple(), to
test that i initialize pgbench with a scale of 40.
In that context pgstattuple() tooks 1.4s to process pgbench_account
table and relation_free_space() tooks 730ms (half the time!)
In the index the difference is less notorious, 170ms the former and
150ms the latter.

3) pgstattuple-stats_target.patch
This patch adds a stats_target parameter to the relation_free_space()
function, it mimics the way analyze choose the blocks to read and is
faster than plain relation_free_space() but of course could be inexact
if the pages that we don't read are the ones with more free space

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

Attachment: pgstattuple-stats_target.patch
Description: text/x-patch (8.2 KB)
Attachment: pgstattuple-relation_free_space.patch
Description: text/x-patch (19.7 KB)
Attachment: pgstattuple-gin_spgist.patch
Description: text/x-patch (8.2 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Jaime CasanovaDate: 2012-02-14 07:10:58
Subject: Re: pg_stats_recovery view
Previous:From: Shigeru HanadaDate: 2012-02-14 06:15:54
Subject: Re: pgsql_fdw, FDW for PostgreSQL server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group