From: patrick keshishian <pkeshish@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Planner doesn't chose Index - (slow select)
Date: Tue, 18 Apr 2006 18:02:27 -0700
Message-ID: <53b425b00604181802v2cc259e1r59f9043b2cea3324@mail.gmail.com> (raw)
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=# select count(*) from pk_c2 b0 where b0.offer_id=7141;
count
-------
1
(1 row)
Time: 5139.004 ms
db=# select count(*) from pk_c2 b0 where b0.pending=true and b0.offer_id=7141;
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=# select count(*) from pk_c2 ;
count
---------
2158094
(1 row)
Time: 5275.782 ms
db=# select count(*) from pk_c2 where pending=true;
count
-------
51
(1 row)
Time: 5073.699 ms
db=# explain select count(*) from pk_c2 b0 where b0.offer_id=7141;
QUERY PLAN
---------------------------------------------------------------------------
Aggregate (cost=44992.78..44992.78 rows=1 width=0)
-> Seq Scan on pk_c2 b0 (cost=0.00..44962.50 rows=12109 width=0)
Filter: (offer_id = 7141)
(3 rows)
Time: 1.350 ms
db=# explain select count(*) from pk_c2 b0 where b0.pending=true and
b0.offer_id=7141;
QUERY PLAN
----------------------------------------------------------------------------------------
Aggregate (cost=45973.10..45973.10 rows=1 width=0)
-> Index Scan using pk_boidx on pk_c2 b0 (cost=0.00..45973.09
rows=1 width=0)
Index Cond: (offer_id = 7141)
Filter: (pending = true)
(4 rows)
Time: 1.784 ms
The table has indexes for both 'offer_id' and '(pending=true)':
Indexes:
"pk_boidx" btree (offer_id)
"pk_bpidx" btree (((pending = 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=# explain select count(*) from pk_c2 b0 where b0.offer_id=7141 and oid > 1;
QUERY PLAN
-------------------------------------------------------------------------------------------
Aggregate (cost=45983.19..45983.19 rows=1 width=0)
-> Index Scan using pk_boidx on pk_c2 b0 (cost=0.00..45973.09
rows=4037 width=0)
Index Cond: (offer_id = 7141)
Filter: (oid > 1::oid)
(4 rows)
Time: 27.301 ms
db=# select count(*) from pk_c2 b0 where b0.offer_id=7141 and oid > 1;
count
-------
1
(1 row)
Time: 1.900 ms
What gives?
This seems just too hokey for my taste.
--patrick
db=# select version();
version
-------------------------------------------------------------------------
PostgreSQL 7.4.12 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pkeshish@gmail.com
Subject: Re: Planner doesn't chose Index - (slow select)
In-Reply-To: <53b425b00604181802v2cc259e1r59f9043b2cea3324@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox