From: | "patrick keshishian" <pkeshish(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Planner doesn't chose Index - (slow select) |
Date: | 2006-04-19 01:02:27 |
Message-ID: | 53b425b00604181802v2cc259e1r59f9043b2cea3324@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-04-19 02:19:44 | Re: Planner doesn't chose Index - (slow select) |
Previous Message | Tom Lane | 2006-04-18 23:38:33 | Re: merge>hash>loop |