Re: Window functions and index usage

On 10/04/2011 05:52 PM, Robert Klemme wrote:
> But then why do require using the second index column in the first
> place? If the data set is small then the query is likely fast if the
> selection via id can use any index.
I mean the fetched dataset is not large, I didn't mean the dataset in
total isn't large. Imagine the commit fest application, but with 10000
comments per patch. You want to fetch the 100 patches in the current
commit fest, and 3 latest comments per patch.
>> And you don't have to fetch
>> them for all threads / patches. You might fetch them only for patches in
>> currently viewed commit fest. See
>> https://commitfest.postgresql.org/action/commitfest_view?id=12 for one such
>> use. What I have in mind is fetching first all the patches in the commit
>> fest in one go. Then issue query which would look something like:
>> select * from
>> (select comment_data, row_number() over (partition by patch_id order by
>> comment_date desc)
>> from patch_comments
>> where patch_id in (list of patch_ids fetched in first query)
>> ) tmp where row_number<= 3;
> Interesting: I notice that I the query cannot successfully be simplified on 8.4:
>
> rklemme=> select *,
> row_number() over (partition by id order by seq desc) as rn
> from test
> where id in (1,2,3)
> and rn<= 3
> ;
That can't be done, where conditions targeting window functions must be
done using subquery. There is no difference in 9.1 as far as I know.

> Again, what is easy for you as a human will likely be quite complex
> for the optimizer (knowing that the order by and the row_number output
> align).
I am not trying to say it is easy.

- Anssi