Re: seq-scan or index-scan

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