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: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

Search Discussions

  • John Arbash Meinel at Feb 1, 2005 at 8:07 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedFeb 1, '05 at 6:55p
activeFeb 1, '05 at 8:07p
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase