Why the difference in query plan and performance pg 7.4.6? - Mailing list pgsql-performance

From Joost Kraaijeveld
Subject Why the difference in query plan and performance pg 7.4.6?
Date
Msg-id A3D1526C98B7C1409A687E0943EAC41001EB47@obelix.askesis.nl
Whole thread Raw
Responses Re: Why the difference in query plan and performance pg
List pgsql-performance
Hi all,

I have a freshly vacuumed table with 1104379 records with a index on zipcode. Can anyone explain why the queries go as
theygo, and why the performance differs so much (1 second versus 64 seconds, or stated differently,  10000 records per
secondversus 1562 records per second) and why the query plan of query 2 ignores the index? 

For completeness sake I also did a select ordernumber without any ordering. That only took 98 second for 1104379 record
(11222record per second, compariable with the first query as I would have expected).  

Query 1:
select a.ordernumer from orders a order by a.zipcode limit 10000
Explain:
QUERY PLAN
Limit  (cost=0.00..39019.79 rows=10000 width=14)
  ->  Index Scan using orders_postcode on orders a  (cost=0.00..4309264.07 rows=1104379 width=14)
Running time: 1 second

Query 2:
select a.ordernumer from orders a order by a.zipcode limit 100000
Explain:
QUERY PLAN
Limit  (cost=207589.75..207839.75 rows=100000 width=14)
  ->  Sort  (cost=207589.75..210350.70 rows=1104379 width=14)
        Sort Key: postcode
        ->  Seq Scan on orders a  (cost=0.00..46808.79 rows=1104379 width=14)
Running time: 64 seconds

Query 3:
select a.ordernumer from orders a
QUERY PLAN
Seq Scan on orders a  (cost=0.00..46808.79 rows=1104379 width=4)
Running time: 98 seconds

Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

pgsql-performance by date:

Previous
From: "Merlin Moncure"
Date:
Subject: Re: High end server and storage for a PostgreSQL OLTP system
Next
From: John Arbash Meinel
Date:
Subject: Re: Why the difference in query plan and performance pg