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: | Raw Message | Whole Thread | 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 |