From: John Arbash Meinel <john@arbash-meinel.com>
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
To: pgsql-performance@postgresql.org
Subject: Re: Why the difference in query plan and performance pg
Date: Tue, 01 Feb 2005 14:06:47 -0600
Message-ID: <41FFE157.8000708@arbash-meinel.com> (raw)
In-Reply-To: <A3D1526C98B7C1409A687E0943EAC41001EB47@obelix.askesis.nl>
References: <A3D1526C98B7C1409A687E0943EAC41001EB47@obelix.askesis.nl>
Joost Kraaijeveld wrote:
>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?
>
>
>
>
Indexes are generally only faster if you are grabbing <10% of the table.
Otherwise you have the overhead of loading the index into memory, and
then paging through it looking for the entries.
With 100,000 entries a sequential scan is actually likely to be faster
than an indexed one.
If you try:
select a.ordernumer from orders a order by a.zipcode
how long does it take?
You can also try disabling sequential scan to see how long Query 2 would
be if you used indexing. Remember, though, that because of caching, a
repeated index scan may seem faster, but in actual production, that
index may not be cached, depending on what other queries are done.
John
=:->
>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: J.Kraaijeveld@Askesis.nl
>web: www.askesis.nl
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, J.Kraaijeveld@Askesis.nl
Subject: Re: Why the difference in query plan and performance pg
In-Reply-To: <41FFE157.8000708@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox