Optimizer picks an ineffient plan

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!!

Responses

Browse pgsql-general by date

  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