as tom pointed out - this is not possible.
there is no limit 20 in my case - i just used it to indicate that limiting does not make the index scan possible which
itdoes in some other cases.
the partial sort thing simon pointed out is what is needed at this point.
many thanks,
hans
On Aug 11, 2010, at 5:29 PM, Alvaro Herrera wrote:
> Excerpts from Hans-Jürgen Schönig's message of mié ago 11 08:21:10 -0400 2010:
>
>> same with limit ...
>>
>>
>> test=# explain analyze select * from t_test order by x, y limit 20;
>
> But if you put the limit in a subquery which is ordered by the
> known-indexed condition, it is very fast:
>
> alvherre=# explain analyze select * from (select * from t_test order by x limit 20) f order by x, y;
> QUERY PLAN
>
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
> Sort (cost=1.24..1.29 rows=20 width=8) (actual time=0.252..0.296 rows=20 loops=1)
> Sort Key: t_test.x, t_test.y
> Sort Method: quicksort Memory: 26kB
> -> Limit (cost=0.00..0.61 rows=20 width=8) (actual time=0.051..0.181 rows=20 loops=1)
> -> Index Scan using idx_aaaaa on t_test (cost=0.00..30408.36 rows=1000000 width=8) (actual
time=0.046..0.098rows=20 loops=1)
> Total runtime: 0.425 ms
> (6 filas)
>
>
> I guess it boils down to being able to sort a smaller result set.
>
> --
> Álvaro Herrera <alvherre@commandprompt.com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de