Message-ID: <48D9943B.9060801@postnewspapers.com.au>
Date: Wed, 24 Sep 2008 09:13:31 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.16 (X11/20080725)
MIME-Version: 1.0
To: Einars <einars@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Chaotically weird execution plan
References: <4f8bd5c90809231753xbcdacd2xfba4d38c2eb34faf@mail.gmail.com>
In-Reply-To: <4f8bd5c90809231753xbcdacd2xfba4d38c2eb34faf@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Einars wrote:
> As query plan shows, it got the
> correct answer, 15888, very fast: the rest of the 13 seconds it's just
> rechecking all the comments for some weird reasons.

I'd already written: "If you need the test for status = 1, consider a
partial index" when I noticed your schema definition:

>     "comments_created_by" btree (created_by) WHERE status = 1

I find it hard to guess why it's having to recheck the WHERE clause
given the use of a partial index that should cover that nicely. I don't
see how it could be a visibility issue (in that I thought tuples were
read and tested for visibility as part of the initial heap scan) but I
don't see what else it could be.

It seems odd to me, so I'm really interested in seeing what others have
to say on this.

--
Craig Ringer