Data caching

From: Martin Chlupac <martin(dot)chlupac(at)rcware(dot)eu>
To: pgsql-performance(at)postgresql(dot)org
Subject: Data caching
Date: 2009-07-09 10:29:24
Message-ID: ecdbb81a0907090329t6e0f1d96he444b34ddb671f26@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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?

Thank you in advance

Martin

# explain analyze select * from
"records_f4f23ca0-9c35-43ac-bb0d-1ef3784399ac" where variable_id=7553
and ts > '2009-07-01 17:00:00' and ts < now() order by ts limit 20000;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3924.13..3928.91 rows=1912 width=206) (actual
time=3687.661..3705.546 rows=2161 loops=1)
-> Sort (cost=3924.13..3928.91 rows=1912 width=206) (actual
time=3687.654..3693.864 rows=2161 loops=1)
Sort Key: ts
Sort Method: quicksort Memory: 400kB
-> 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)
Recheck Cond: ((variable_id = 7553) AND (ts >
'2009-07-01 17:00:00'::timestamp without time zone) AND (ts < now()))
-> Bitmap Index Scan on pokusny_index
(cost=0.00..76.27 rows=1912 width=0) (actual time=304.160..304.160
rows=2687 loops=1)
Index Cond: ((variable_id = 7553) AND (ts >
'2009-07-01 17:00:00'::timestamp without time zone) AND (ts < now()))
Total runtime: 3711.488 ms
(9 rows)

# explain analyze select * from
"records_f4f23ca0-9c35-43ac-bb0d-1ef3784399ac" where variable_id=7553
and ts > '2009-07-01 17:00:00' and ts < now() order by ts limit 20000;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3924.13..3928.91 rows=1912 width=206) (actual
time=18.135..35.140 rows=2161 loops=1)
-> Sort (cost=3924.13..3928.91 rows=1912 width=206) (actual
time=18.127..24.064 rows=2161 loops=1)
Sort Key: ts
Sort Method: quicksort Memory: 400kB
-> 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)
Recheck Cond: ((variable_id = 7553) AND (ts >
'2009-07-01 17:00:00'::timestamp without time zone) AND (ts < now()))
-> Bitmap Index Scan on pokusny_index
(cost=0.00..76.27 rows=1912 width=0) (actual time=1.352..1.352
rows=2687 loops=1)
Index Cond: ((variable_id = 7553) AND (ts >
'2009-07-01 17:00:00'::timestamp without time zone) AND (ts < now()))
Total runtime: 40.971 ms
(9 rows)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2009-07-09 10:52:54 Re: Data caching
Previous Message Franclin Foping 2009-07-08 16:27:32 Maximum size of an XML document