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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
