Hi all, I have a freshly vacuumed table with 1104379 records with a index on zipcode. Can anyone explain why the queries go as they go, and why the performance differs so much (1 second versus 64 seconds, or stated differently, 10000 records per second versus 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 (11222 record 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: [EMAIL PROTECTED] web: www.askesis.nl ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend