Message-ID: <4E8B1FA7.50607@thl.fi>
Date: Tue, 4 Oct 2011 18:00:55 +0300
From: =?ISO-8859-1?Q?Anssi_K=E4=E4ri=E4inen?= <anssi.kaariainen@thl.fi>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.23) Gecko/20110921 Lightning/1.0b2 Thunderbird/3.1.15
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Window functions and index usage
References: <4E8AD46F.30003@thl.fi> <7900.1317739011@sss.pgh.pa.us>
In-Reply-To: <7900.1317739011@sss.pgh.pa.us>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable

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 orde=
r
> 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 s=
o.
I tested it and yes, it can use the index scan. But not in the way I=20
though it would be used.
> Note that it cannot use the index for both ordering and satisfying the
> IN condition.  If it used the =3DANY 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=20
this query as get 5 first values for id 1, get 5 first values for id 2,=20
get 5 first values for id 3. At least if I do this by hand using UNION I=20
get two orders of magnitude faster execution time. I am not saying it=20
would be easy to do that, but to me it seems it would be possible to use=20
the index more efficiently for the example query. Or is the following=20
UNION query not equivalent to the window function query, assuming I am=20
not interested in the row_number column itself?

(select id, seq from test where id =3D 1 order by seq limit 5)
union
(select id, seq from test where id =3D 2 order by seq limit 5)
union
(select id, seq from test where id =3D 3 order by seq limit 5);

The results are in different order, but there is no order by in the=20
original query except in the OVER clause, so it should not matter.

  - Anssi K=E4=E4ri=E4inen