| From: | "Bupp Phillips" <hello(at)noname(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Optimizer picks an ineffient plan |
| Date: | 2003-09-02 02:29:12 |
| Message-ID: | bj0v98$1f1q$1@news.hub.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
I have a customer table that has the field CUSTOMER_ID as the primary key
(cust_pkkey), the table has 102,834 records in it.
The following select statement works fine:
select * from customer order by customer_id;
QUERY PLAN:
Index Scan using cust_pkkey on customer (cost=0.00..5175.17 rows=102834
width=724)
Total runtime: 5999.47 msec
but...
select * from customer order by customer_id, first_name;
QUERY PLAN:
Sort(cost=142028.25..142028.25 rows=102834 width=724)
-> Seq Scan on customer (cost=0.00..4617.34 rows=102834 width=724)
Total runtime: 19999.81 msec
It seems that the optimizer should be able to detect (in this instance at
least) that the first order by field is a primary key and should not
consider the other fields because it's pointless... the resultset will be in
<primary key> order.
NOTE: I'm testing this on Postgresql 7.2 for Windows, so this my have
already been dealt with.
Thanks and keep up the great work!!
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2003-09-02 02:44:16 | Re: adding SERIAL to a table |
| Previous Message | Lamar Owen | 2003-09-02 02:27:24 | Re: Commercial postgresql |