From: Anssi Kääriäinen <anssi.kaariainen@thl.fi>
To: Robert Klemme <shortcutter@googlemail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Window functions and index usage
Date: Tue, 4 Oct 2011 18:05:10 +0300
Message-ID: <4E8B20A6.4090104@thl.fi> (raw)
In-Reply-To: <CAM9pMnPds50eKum7LWyxxXFGyiK=XX8cdm=2z53KZcsUKopp2g@mail.gmail.com>
References: <4E8AD46F.30003@thl.fi>
<CAM9pMnPOkMLCH6b8ToHY9ui-6LFKmwXZNzPNyLoDEVRzB-fQZg@mail.gmail.com>
<4E8B12F5.20308@thl.fi>
<CAM9pMnPds50eKum7LWyxxXFGyiK=XX8cdm=2z53KZcsUKopp2g@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: anssi.kaariainen@thl.fi, shortcutter@googlemail.com
Subject: Re: Window functions and index usage
In-Reply-To: <4E8B20A6.4090104@thl.fi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox