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)
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 |