On 3 Červenec 2012, 17:58, Andreas Kretschmer wrote: > Dear list, > > i have a table and i'm selecting all records without a where-condition, > and i don't need a ORDER BY: > > > > production=*# explain analyse select * from boxes; > QUERY PLAN > --------------------------------------------------------------------------------------------------------------- > Seq Scan on boxes (cost=0.00..990783.99 rows=6499 width=581) (actual > time=6.514..4588.136 rows=3060 loops=1) > Total runtime: 4588.729 ms > (2 rows) > > > It's slow, so i tried with an ORDER BY: > > > > production=# explain analyse select * from boxes order by id; > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------- > Index Scan using boxes_pkey on boxes (cost=0.00..162437.00 rows=6499 > width=581) (actual time=0.065..55.730 rows=3060 loops=1) > Total runtime: 56.169 ms > (2 rows) > > > Why not using the index (it's a primary key) for the first query? I suppose the second run was cached, i.e. most of the data was in the page cache. And it's not that PostgreSQL can read "just" the index - it needs to check tuple visibility which is stored in the table. Try to do "sync && echo 3 > /proc/sys/vm/drop_caches" and rerun the second query. How did that work? How much data are we talking about anyway? How much RAM is in the server? Tomas -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
