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