Re: Window functions and index usage

=?ISO-8859-1?Q?Anssi_K=E4=E4ri=E4inen?= <anssi(dot)kaariainen(at)thl(dot)fi> writes:
> I have the following setup:
> create table test(id integer, seq integer);
> insert into test select generate_series(0, 100), generate_series(0, 1000);
> create unique index test_idx on test(id, seq);
> analyze test;

> Now I try to fetch the latest 5 values per id, ordered by seq from the
> table:

> select * from (
> select id, seq, row_number() over (partition by id order by seq)
> from test
> where id in (1, 2, 3)
> ) where row_number() <= 5;

> This does not use the index on id, seq for sorting the data. It uses a
> bitmap index scan, and sequential scan when issued SET enable_bitmapscan
> to false.

The cost estimates I get are 806 for bitmap scan and sort, 2097 for
seqscan and sort, 4890 for indexscan without sort. It *can* use the
index for that query ... it just doesn't think it's a good idea. It's
probably right, too. At least, the actual runtimes go in the same order
on my machine. Seqscan-and-sort very often beats an indexscan for
sorting a table, unless the table is clustered on the index or nearly so.

Note that it cannot use the index for both ordering and satisfying the
IN condition. If it used the =ANY clause as an index condition, what
that would imply is three separate index searches and so the results
wouldn't necessarily be correctly ordered. This is why the plain
indexscan costs out so expensive: it's a full-table scan.

regards, tom lane