Date: Thu, 07 Jun 2007 12:01:51 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Weird 8.2.4 performance
In-reply-to: <371D1936-E96B-4865-AEEF-C82AC41C2982@hotdogrecords.com>
To: Kurt Overberg <kurt@hotdogrecords.com>
Cc: pgsql-performance@postgresql.org
Message-id: <46674AEF.6040803@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <371D1936-E96B-4865-AEEF-C82AC41C2982@hotdogrecords.com>
User-Agent: Thunderbird 1.5.0.10 (X11/20070313)

Kurt Overberg wrote:

> Explain Outputs:
> 
> -- 8.2
> 
> 

>    ->  Bitmap Heap Scan on taskinstance  (cost=20.71..2143.26 rows=556 
> width=8) (actual time=421.423..5655.745 rows=98 loops=9)
>          Recheck Cond: (taskinstance.taskid = task.id)
>          ->  Bitmap Index Scan on taskid_taskinstance_key  
> (cost=0.00..20.57 rows=556 width=0) (actual time=54.709..54.709 rows=196 
> loops=9)

> -- 8.0
> 

>    ->  Index Scan using taskid_taskinstance_key on taskinstance  
> (cost=0.00..2152.28 rows=563 width=8) (actual time=0.012..0.832 rows=145 
> loops=11)


8.2 is deciding to use a bitmap index scan on taskid_taskinstance_key, 
which seems to be slower (!) than a plain old index scan that 8.0 is 
using. A dirty work around is to disable bitmap scans via:

SET enable_bitmapscan=off

but it is probably worthwhile to try to find out *why* the bitmap scan 
is 1) slow and 2) chosen at all given 1).

One thought that comes to mind - is work_mem smaller on your 8.2 system 
than the 8.0 one? (or in fact is it very small on both?). Also it might 
be interesting to see your non-default postgresql.conf settings for both 
systems.

Cheers

Mark