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

Re: When/if to Reindex

From: "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Mark Kirkwood" <markir(at)paradise(dot)net(dot)nz>, "Gregory Stark" <stark(at)enterprisedb(dot)com>, "Bill Moran" <wmoran(at)collaborativefusion(dot)com>, "Vivek Khera" <vivek(at)khera(dot)org>, "Pgsql performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: When/if to Reindex
Date: 2007-08-24 16:56:20
Message-ID: 357fa7590708240956k18e3ba7dv735cdaa99a667312@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 8/24/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> writes:
> > Why do we even need to consider calling RelationGetNumberOfBlocks or
> looking
> > at the pg_class.relpages entry?  My understanding of the expected
> behaviour
> > is that while a reindex is happening, all queries run against the parent
> > table are planned as though the index isn't there (i.e. it's unusable).
>
> Where in the world did you get that idea?


Maybe that's what I was *hoping* the behaviour would be. :)

From the docs:
"REINDEX locks out writes but not reads of the index's parent table."
"It also takes an exclusive lock on the specific index being processed..."

I believe those two statements imply that reads of the parent table don't
take any lock whatsoever on the index being processed, i.e. they ignore it.

If we had a REINDEX CONCURRENTLY it might work that way.  A normal
> REINDEX cannot "mark" anything because it runs within a single
> transaction; there is no way that it can emit any catalog changes
> that will be visible before it's over.
>
... but I understand this difficulty.

So, can we simply trust what's in pg_class.relpages and ignore looking
directly at the index?  This is a fairly serious concern for us, that
reindex is blocking all readers of the parent table.

Thanks,
Steve

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-08-24 17:28:55
Subject: Re: When/if to Reindex
Previous:From: Luke LonerganDate: 2007-08-24 16:25:53
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

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