DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=dCuXkHVBvtEjZbZb96TOvH7ORS3qSwHxkPT/yG20NF5/2y8GOeAbQndcmOdxuWL1C+29iOBmDh4FijAat7LOnhxeWGsE/2CPs0ogf3AQ3If3E7/txdJhgEF7MaFZUXqHZr3NeBav5JN2PBDzq+Aykx3E3EWqz0DoxgXTa38jU9w=
Message-ID: <53b425b00604181802v2cc259e1r59f9043b2cea3324@mail.gmail.com>
Date: Tue, 18 Apr 2006 18:02:27 -0700
From: "patrick keshishian" <pkeshish@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Planner doesn't chose Index - (slow select)
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all,

I've been struggling with some performance issues with certain
SQL queries.  I was prepping a long-ish overview of my problem
to submit, but I think I'll start out with a simple case of the
problem first, hopefully answers I receive will help me solve
my initial issue.

Consider the following two queries which yield drastically different
run-time:

db=3D# select count(*) from pk_c2 b0 where b0.offer_id=3D7141;
 count
-------
     1
(1 row)
Time: 5139.004 ms

db=3D# select count(*) from pk_c2 b0 where b0.pending=3Dtrue and b0.offer_i=
d=3D7141;
 count
-------
     1
(1 row)
Time: 1.828 ms


That's 2811 times faster!

Just to give you an idea of size of pk_c2 table:

db=3D# select count(*) from pk_c2 ;
  count
---------
 2158094
(1 row)
Time: 5275.782 ms

db=3D# select count(*) from pk_c2 where pending=3Dtrue;
 count
-------
    51
(1 row)
Time: 5073.699 ms


db=3D# explain select count(*) from pk_c2 b0 where b0.offer_id=3D7141;
QUERY PLAN
---------------------------------------------------------------------------
 Aggregate  (cost=3D44992.78..44992.78 rows=3D1 width=3D0)
   ->  Seq Scan on pk_c2 b0  (cost=3D0.00..44962.50 rows=3D12109 width=3D0)
         Filter: (offer_id =3D 7141)
(3 rows)
Time: 1.350 ms

db=3D# explain select count(*) from pk_c2 b0 where b0.pending=3Dtrue and
b0.offer_id=3D7141;
QUERY PLAN
---------------------------------------------------------------------------=
-------------
 Aggregate  (cost=3D45973.10..45973.10 rows=3D1 width=3D0)
   ->  Index Scan using pk_boidx on pk_c2 b0  (cost=3D0.00..45973.09
rows=3D1 width=3D0)
         Index Cond: (offer_id =3D 7141)
         Filter: (pending =3D true)
(4 rows)
Time: 1.784 ms


The table has indexes for both 'offer_id' and '(pending=3Dtrue)':

Indexes:
    "pk_boidx" btree (offer_id)
    "pk_bpidx" btree (((pending =3D true)))

So, why would the planner chose to use the index on the second query
and not on the first?


Note that I am able to fool the planner into using an Index scan
on offer_id by adding a silly new condition in the where clause of
the first form of the query:


db=3D# explain select count(*) from pk_c2 b0 where b0.offer_id=3D7141 and o=
id > 1;
QUERY PLAN
---------------------------------------------------------------------------=
----------------
 Aggregate  (cost=3D45983.19..45983.19 rows=3D1 width=3D0)
   ->  Index Scan using pk_boidx on pk_c2 b0  (cost=3D0.00..45973.09
rows=3D4037 width=3D0)
         Index Cond: (offer_id =3D 7141)
         Filter: (oid > 1::oid)
(4 rows)
Time: 27.301 ms

db=3D# select count(*) from pk_c2 b0 where b0.offer_id=3D7141 and oid > 1;
 count
-------
     1
(1 row)
Time: 1.900 ms

What gives?

This seems just too hokey for my taste.

--patrick


db=3D# select version();
                                 version
-------------------------------------------------------------------------
 PostgreSQL 7.4.12 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6