Query in 9.0.2 not using index in 9.0.0 works fine

From: "Matthias Howell" <Matthias(dot)Howell(at)voxco(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Query in 9.0.2 not using index in 9.0.0 works fine
Date: 2011-07-05 20:50:22
Message-ID: 64642EE13118B646967594D0CC650A1801E448C0@Mailvm.submtl.voxco.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I've just copied a database from one linux machine to another.

"Fast" machine is CentOS 5.5, running postgres 9.0.0 64 bit

"Slow" machine is Red Hat 5.5 running postgres 9.0.2 64 bit.

Here's the query:

explain analyze select sentenceid from sentences where sentenceid = any
( array(select sentenceid from sentences where docid =
any(array[696374,696377])))

on the fast machine this is the explain:

"Bitmap Heap Scan on sentences (cost=924.41..964.47 rows=10 width=8)
(actual time=0.748..0.800 rows=41 loops=1)"

" Recheck Cond: (sentenceid = ANY ($0))"

" InitPlan 1 (returns $0)"

" -> Bitmap Heap Scan on sentences (cost=12.93..879.27 rows=220
width=8) (actual time=0.199..0.446 rows=41 loops=1)"

" Recheck Cond: (docid = ANY ('{696374,696377}'::bigint[]))"

" -> Bitmap Index Scan on sentdocs (cost=0.00..12.87 rows=220
width=0) (actual time=0.134..0.134 rows=41 loops=1)"

" Index Cond: (docid = ANY
('{696374,696377}'::bigint[]))"

" -> Bitmap Index Scan on pk_sentences (cost=0.00..45.14 rows=10
width=0) (actual time=0.741..0.741 rows=41 loops=1)"

" Index Cond: (sentenceid = ANY ($0))"

"Total runtime: 0.925 ms"

And on the slow machine:

"Seq Scan on sentences (cost=10000000608.90..10000445893.60 rows=10
width=8) (actual time=2679.412..6372.393 rows=41 loops=1)"

" Filter: (sentenceid = ANY ($0))"

" InitPlan 1 (returns $0)"

" -> Bitmap Heap Scan on sentences (cost=10.73..608.90 rows=152
width=8) (actual time=0.044..0.076 rows=41 loops=1)"

" Recheck Cond: (docid = ANY ('{696374,696377}'::integer[]))"

" -> Bitmap Index Scan on sentdocs (cost=0.00..10.69 rows=152
width=0) (actual time=0.037..0.037 rows=41 loops=1)"

" Index Cond: (docid = ANY
('{696374,696377}'::integer[]))"

"Total runtime: 6372.468 ms"

The configurations were identical initially, I've changed those on the
slow machine but to no avail.

there is an index on sentences on the docid in both systems.

I'm at quite a loss as to how/why this is occurring and what to do about
it.

I tried disabling seqscan on the slow machine but that also made no
difference.

Any help/ideas much appreciated.

Matthias

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jonathan 2011-07-06 00:18:10 Slow query when using ORDER BY *and* LIMIT
Previous Message Greg Smith 2011-07-05 17:43:26 Re: bitmask index