From: Anssi Kääriäinen <anssi.kaariainen@thl.fi>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Window functions and index usage
Date: Tue, 4 Oct 2011 18:00:55 +0300
Message-ID: <4E8B1FA7.50607@thl.fi> (raw)
In-Reply-To: <7900.1317739011@sss.pgh.pa.us>
References: <4E8AD46F.30003@thl.fi>
<7900.1317739011@sss.pgh.pa.us>
On 10/04/2011 05:36 PM, Tom Lane wrote:
> 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.
I tested it and yes, it can use the index scan. But not in the way I
though it would be used.
> 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.
This I don't understand. I would imagine it would be possible to execute
this query as get 5 first values for id 1, get 5 first values for id 2,
get 5 first values for id 3. At least if I do this by hand using UNION I
get two orders of magnitude faster execution time. I am not saying it
would be easy to do that, but to me it seems it would be possible to use
the index more efficiently for the example query. Or is the following
UNION query not equivalent to the window function query, assuming I am
not interested in the row_number column itself?
(select id, seq from test where id = 1 order by seq limit 5)
union
(select id, seq from test where id = 2 order by seq limit 5)
union
(select id, seq from test where id = 3 order by seq limit 5);
The results are in different order, but there is no order by in the
original query except in the OVER clause, so it should not matter.
- Anssi Kääriäinen
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, tgl@sss.pgh.pa.us
Subject: Re: Window functions and index usage
In-Reply-To: <4E8B1FA7.50607@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