Re: Data caching

Martin Chlupac wrote:
> Hello everybody,
> I have a simple query which selects data from not very large table (
> 434161 rows) and takes far more time than I'd expect. I believe it's
> due to a poor disk performance because when I execute the very same
> query for a second time I get much better results (caching kicks in?).
> Can you please confirm my theory or do you see any other possible
> explanation?

Yep - it's the difference between fetching from memory and from disk.

> -> Bitmap Heap Scan on
> "records_f4f23ca0-9c35-43ac-bb0d-1ef3784399ac" (cost=76.75..3819.91
> rows=1912 width=206) (actual time=329.416..3677.521 rows=2161 loops=1)

> -> Bitmap Heap Scan on
> "records_f4f23ca0-9c35-43ac-bb0d-1ef3784399ac" (cost=76.75..3819.91
> rows=1912 width=206) (actual time=1.616..10.369 rows=2161 loops=1)

The plan scans the index, and builds up a bitmap of which disk-blocks
contain (potential) matches. It then has to read the blocks (the heap
scan above), confirm they match and then return the rows. If you look at
the "actual time" above you can see about 90% of the slow query is spent
doing this.

--
Richard Huxton
Archonet Ltd