Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id A99079F9C76 for ; Thu, 23 Aug 2007 19:25:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 77993-03 for ; Thu, 23 Aug 2007 19:25:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id 328329F9C4F for ; Thu, 23 Aug 2007 19:25:26 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id f5so579020nfh for ; Thu, 23 Aug 2007 15:25:25 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=au4Y/YU2YYmCbMv3h5HX0TyhjA56a5VSO5JRe7rMmBUZN6QZFaaId52fujh63zSTZo7uxE4U8q8gMZCQvQ59fKMk4QxNnlQJ+nipEhr13BWGbkD9qJq5zfIi1RzkEINRf8wL5LiEngKbAKI0RCPAcmk6tGammmoOrr7YcyyrKb8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=nwmJxZ1oAyAeT2zyoXQdxBuZMTgB5dNOALF1+6vap5GEej2JF57NtrxfP6K022p2nYaySc7FQ+g4FXcNw7AtQZ8/kkrl7qPAUOmO0RvxZWAvct/07WyU0Ey4GNNuUH7f9DlQtYpVYsu95uuC1Jecq2iv2oACUdbwimdymNYd148= Received: by 10.78.81.20 with SMTP id e20mr1513298hub.1187907925127; Thu, 23 Aug 2007 15:25:25 -0700 (PDT) Received: by 10.78.134.7 with HTTP; Thu, 23 Aug 2007 15:25:25 -0700 (PDT) Message-ID: <357fa7590708231525h2f48ce4dw3e48ecca5ccddf72@mail.gmail.com> Date: Thu, 23 Aug 2007 18:25:25 -0400 From: "Steven Flatt" To: "Gregory Stark" Subject: Re: When/if to Reindex Cc: "Tom Lane" , "Bill Moran" , "Vivek Khera" , "Pgsql performance" In-Reply-To: <873aybw2le.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_242920_3747624.1187907925060" References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com> <38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org> <357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com> <20070808152757.9eb9bbcc.wmoran@collaborativefusion.com> <11801.1186624308@sss.pgh.pa.us> <357fa7590708090651p71b9bc47n66739f8dbbb4ff57@mail.gmail.com> <357fa7590708220955h441cd75ap4c0f0342a1c30a14@mail.gmail.com> <873aybw2le.fsf@oxford.xeocode.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/276 X-Sequence-Number: 26406 ------=_Part_242920_3747624.1187907925060 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/22/07, Gregory Stark wrote: > postgres=# create table test (i integer); > CREATE TABLE > postgres=# insert into test select generate_series(1,1000); > INSERT 0 1000 > postgres=# create or replace function slow(integer) returns integer as > 'begin perform pg_sleep(0); return $1; end' language plpgsql immutable > strict; > CREATE FUNCTION > postgres=# create index slowi on test (slow(i)); > CREATE INDEX > postgres=# create or replace function slow(integer) returns integer as > 'begin perform pg_sleep(1); return $1; end' language plpgsql immutable > strict; > CREATE FUNCTION > postgres=# reindex index slowi; > > While that's running I ran: > > postgres=# select count(*) from test; > count > ------- > 1000 > (1 row) Interestingly enough, the example you've given does not work for me either. The select count(*) from test blocks until the reindex completes. Are we using the same pg version? # select version(); version -------------------------------------------------------------------------------- ---------------- PostgreSQL 8.2.4 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4[FreeBSD] 20050518 (1 row) Looking at the pg_locks table, I see: # select locktype,relation,mode,granted from pg_locks where not granted; locktype | relation | mode | granted ----------+----------+-----------------+--------- relation | 69293 | AccessShareLock | f (1 row) # select relname from pg_class where oid = 69293; relname --------- slowi (1 row) # select locktype,relation,mode,granted from pg_locks where relation = 69293; locktype | relation | mode | granted ----------+----------+---------------------+--------- relation | 69293 | AccessShareLock | f relation | 69293 | AccessExclusiveLock | t (2 rows) So the reindex statement has an AccessExclusiveLock on the index, which seems right, and this blocks the select count(*) from getting an AccessShareLock on the index. Why does the select count(*) need a lock on the index? Is there some Postgres setting that could cause this behaviour? I can't even do an "explain select count(*) from test" without blocking. Any ideas? Steve ------=_Part_242920_3747624.1187907925060 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
On 8/22/07, Gregory Stark <stark@enterprisedb.com> wrote:
postgres=# create table test (i integer);
CREATE TABLE
postgres=# insert into test select generate_series(1,1000);
INSERT 0 1000
postgres=# create or replace function slow(integer) returns integer as 'begin perform pg_sleep(0); return $1; end' language plpgsql immutable strict;
CREATE FUNCTION
postgres=# create index slowi on test (slow(i));
CREATE INDEX
postgres=# create or replace function slow(integer) returns integer as 'begin perform pg_sleep(1); return $1; end' language plpgsql immutable strict;
CREATE FUNCTION
postgres=# reindex index slowi;

While that's running I ran:

postgres=# select count(*) from test;
count
-------
1000
(1 row)
 
Interestingly enough, the example you've given does not work for me either.  The select count(*) from test blocks until the reindex completes.  Are we using the same pg version?
 
# select version();

                                            version

--------------------------------------------------------------------------------
----------------
 PostgreSQL 8.2.4 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518
(1 row)

Looking at the pg_locks table, I see:
 

# select locktype,relation,mode,granted from pg_locks where not granted;
 locktype | relation |      mode       | granted
----------+----------+-----------------+---------
 relation |    69293 | AccessShareLock | f
(1 row)

# select relname from pg_class where oid = 69293;
 relname
---------
 slowi
(1 row)

# select locktype,relation,mode,granted from pg_locks where relation = 69293;
 locktype | relation |        mode         | granted
----------+----------+---------------------+---------
 relation |    69293 | AccessShareLock     | f
 relation |    69293 | AccessExclusiveLock | t
(2 rows)

So the reindex statement has an AccessExclusiveLock on the index, which seems right, and this blocks the select count(*) from getting an AccessShareLock on the index.  Why does the select count(*) need a lock on the index?  Is there some Postgres setting that could cause this behaviour?  I can't even do an "explain select count(*) from test" without blocking.
 
Any ideas?
 
Steve
 
------=_Part_242920_3747624.1187907925060--