Re: Suggestion for aggregate function

On 17 Jan 2003 19:08:06 -0500, Greg Stark <gsstark(at)mit(dot)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_x1 on sale (cost=0.00..386.64 rows=10240 width=12)
(actual time=0.90..399.52 rows=10240 loops=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.48 rows=10240 width=12)
(actual time=941.71..1061.93 rows=10240 loops=1)
-> Seq Scan on sale (cost=0.00..163.40 rows=10240 width=12)
(actual time=0.37..273.41 rows=10240 loops=1)
Total runtime: 1304.63 msec

Servus
Manfred