Re: Suggestion for aggregate function

On 17 Jan 2003 19:08:06 -0500, Greg Stark <gsstark@mit.edu> wrote:
>Would this query be efficient if there's an index on item_id, price ? That is,
>would it know to do an index scan

Yes, at least to avoid the sort step.

> and be able to skip to the next item_id in
>the index as soon as a price was found?

I don't think so.  Look at how the index scan retrieves all rows:

=> EXPLAIN ANALYZE
-> SELECT DISTINCT ON (item) item, price, store FROM sale ORDER BY item, price;
NOTICE:  QUERY PLAN:

Unique  (cost=0.00..412.24 rows=1024 width=12)       (actual time=0.93..549.95 rows=101 loops=1) ->  Index Scan using
s_x1on sale  (cost=0.00..386.64 rows=10240 width=12)                                    (actual time=0.90..399.52
rows=10240loops=1)
 
Total runtime: 551.55 msec

EXPLAIN
=> DROP INDEX s_x1;
DROP
=> EXPLAIN ANALYZE
-> SELECT DISTINCT ON (item) item, price, store FROM sale ORDER BY item, price;
NOTICE:  QUERY PLAN:

Unique  (cost=845.48..871.08 rows=1024 width=12)       (actual time=941.83..1152.25 rows=101 loops=1) ->  Sort
(cost=845.48..845.48rows=10240 width=12)           (actual time=941.71..1061.93 rows=10240 loops=1)       ->  Seq Scan
onsale  (cost=0.00..163.40 rows=10240 width=12)                             (actual time=0.37..273.41 rows=10240
loops=1)
Total runtime: 1304.63 msec

ServusManfred