Re: Weird 8.2.4 performance

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