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

Re: Thoughts on maintaining 7.3

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
Cc: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Thoughts on maintaining 7.3
Date: 2003-10-01 23:09:10
Message-ID: 10509.1065049750@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> I think what Tom is concerned about is that this hasn't been tested
> enough with big datasets.  Also there a little loss of index pages but
> it's much less (orders of magnitude, I think) than what was before.
> This is because the index won't shrink "vertically".

The fact that we won't remove levels shouldn't be meaningful at all ---
I mean, if the index was once big enough to require a dozen btree
levels, and you delete everything, are you going to be upset that it
drops to 13 pages rather than 2?  I doubt it.

The reason I'm waffling about whether the problem is completely fixed or
not is that the existing code will only remove-and-recycle completely
empty btree pages.  As long as you have one key left on a page it will
stay there.  So you could end up with ridiculously low percentage-filled
situations.  This could be fixed by collapsing together adjacent
more-than-half-empty pages, but we ran into a lot of problems trying to
do that in a concurrent fashion.  So I'm waiting to find out if real
usage patterns have a significant issue with this or not.

For example, if you have a timestamp index and you routinely clean out
all entries older than N-days-ago, you won't have a problem in 7.4.
If your pattern is to delete nine out of every ten entries (maybe you
drop minute-by-minute entries and keep only hourly entries after awhile)
then you might find the index loading getting unpleasantly low.  We'll
have to see whether it's a problem in practice.  I'm willing to revisit
the page-merging problem if it's proven to be a real practical problem,
but it looked hard enough that I think it's more profitable to spend the
development effort elsewhere until it's proven necessary.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Stephan SzaboDate: 2003-10-01 23:11:25
Subject: Re: invalid tid errors in latest 7.3.4 stable.
Previous:From: Tom LaneDate: 2003-10-01 22:57:05
Subject: Re: query plan different for "SELECT ..." and "DECLARE CURSOR ..."?

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