From: Simon Riggs <simon@2ndquadrant.com>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index scan startup time
Date: Thu, 30 Mar 2006 18:19:11 +0100
Message-ID: <1143739151.13549.112.camel@localhost.localdomain> (raw)
In-Reply-To: <200603301359.11038.peter_e@gmx.net>
References: <200603301359.11038.peter_e@gmx.net>
On Thu, 2006-03-30 at 13:59 +0200, Peter Eisentraut wrote:
> Can anyone explain this:
>
> EXPLAIN ANALYZE select activity_id from activity where state in (10000, 10001)
> order by activity_id limit 100;
>
> QUERY PLAN
>
> Limit (cost=0.00..622.72 rows=100 width=8) (actual
> time=207356.054..207356.876 rows=100 loops=1)
> -> Index Scan using activity_pk on activity (cost=0.00..40717259.91
> rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1)
> Filter: ((state = 10000) OR (state = 10001))
> Total runtime: 207357.000 ms
>
...just adding to Tom's comments:
The interesting thing about this query is it *looks* like the index is
being used to retrieve the matching rows and so the startup time looks
wrong. However the index is being used instead of a sort to satisfy the
ORDER BY, with the state clauses being applied as after-scan filters
since those columns aren't part of the index. So the Index Scan starts
at the leftmost page and scans the whole index...
If the query had chosen a sort, the startup time would have been easily
understandable, but there's no indication from the EXPLAIN as to why the
Index Scan exists.
Perhaps it should be a TODO item to make the EXPLAIN say explicitly when
an Index Scan is being used to provide sorted output?
Best Regards, Simon Riggs
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: simon@2ndquadrant.com, peter_e@gmx.net
Subject: Re: Index scan startup time
In-Reply-To: <1143739151.13549.112.camel@localhost.localdomain>
* 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