Actually I'd like to add something to this. I think I've found a bug in the current implementation. Would someone be able to check?
Given a table definition of (market text, feedcode text, updated_at timestamptz, value float8) and an index on (market, feedcode, updated_at desc) (note that this table slightly deviates from what I described in my previous mail) and filling it with data.
The following query uses an index skip scan and returns just 1 row (incorrect!)
select distinct on (market, feedcode) market, feedcode
from streams.base_price
where market='TEST'
The following query still uses the regular index scan and returns many more rows (correct)
select distinct on (market, feedcode) *
from streams.base_price
where market='TEST'
It seems that partially filtering on one of the distinct columns triggers incorrect behavior where too many rows in the index are skipped.
-Floris
