Re: Index scan startup time

From: Simon Riggs
Subject: Re: Index scan startup time
Date: ,
Msg-id: 1143739151.13549.112.camel@localhost.localdomain
(view: Whole thread, Raw)
In response to: Index scan startup time  (Peter Eisentraut)
List: pgsql-performance

Tree view

Index scan startup time  (Peter Eisentraut, )
 Re: Index scan startup time  ("Steinar H. Gunderson", )
  Re: Index scan startup time  (Peter Eisentraut, )
   Re: Index scan startup time  ("Steinar H. Gunderson", )
    Re: Index scan startup time  (Michael Stone, )
     Re: Index scan startup time  ("Steinar H. Gunderson", )
      Re: Index scan startup time  (Michael Stone, )
       Re: Index scan startup time  (Tom Lane, )
        Re: Index scan startup time  (Peter Eisentraut, )
         Re: Index scan startup time  (Tom Lane, )
    Re: Index scan startup time  (Peter Eisentraut, )
     Re: Index scan startup time  ("Steinar H. Gunderson", )
 Re: Index scan startup time  (Michael Stone, )
  Re: Index scan startup time  (Peter Eisentraut, )
   Re: Index scan startup time  (Markus Schaber, )
 Re: Index scan startup time  (Simon Riggs, )

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



pgsql-performance by date:

From: Chris Browne
Date:
Subject: Re: Decide between Postgresql and Mysql (help of
From: Philippe Marzin
Date:
Subject: Re: Decide between Postgresql and Mysql (help of