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-01-24 16:24:08
Message-ID: CAJKUy5jWD-nDEzzX6x7H8TMO3uRhV=AdOF2Sr8Wiz+ecCfzTDg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Jan 23, 2012 at 7:18 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Mon, Jan 23, 2012 at 04:56:24PM -0300, Alvaro Herrera wrote:
>>
>> Hm.  Leaf pages hold as much tuples as non-leaf pages, no?  I mean
>> for each page element there's a value and a CTID.  In non-leaf those
>> CTIDs point to other index pages, one level down the tree; in leaf pages
>> they point to the heap.
>
> That distinction seemed important when I sent my last message, but now I agree
> that it's largely irrelevant for free space purposes.  If someone feels like
> doing it, +1 for making pgstattuple() count non-leaf free space.
>

actually i agreed that non-leaf pages are irrelevant... i just
confirmed that in a production system with 300GB none of the indexes
in an 84M rows table nor in a heavily updated one has more than 1 root
page, all the rest are deleted, half_dead or leaf. so the posibility
of bloat coming from non-leaf pages seems very odd

but the possibility of bloat coming from the meta page doesn't exist,
AFAIUI at least

we need the most accurate value about usable free space, because the
idea is to add a sampler mode to the function so we don't scan the
whole relation. that's why we still need the function.

btw... pgstattuple also has the problem that it's not using a ring buffer


attached are two patches:
- v5: is the same original patch but only track space in leaf, deleted
and half_dead pages
- v5.1: adds the same for all kind of indexes (problem is that this is
inconsistent with the fact that pageinspect only manages btree indexes
for everything else)

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

Attachment: relation_free_space-v5.1.patch
Description: text/x-patch (13.8 KB)
Attachment: relation_free_space-v5.patch
Description: text/x-patch (12.1 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-01-24 16:25:59
Subject: Re: Multithread Query Planner
Previous:From: Merlin MoncureDate: 2012-01-24 16:16:02
Subject: Re: GUC_REPORT for protocol tunables was: Re: Optimize binary serialization format of arrays with fixed size elements

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