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.098 rows=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 <[email protected]>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
