On 17 Jan 2003 19:08:06 -0500, Greg Stark <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
