hello pg'lers,
we are facing a puzzle, and was wondering if you could advise?
db has been just vacuumed:
table as:
CREATE TABLE bigdb.t_rrddata
(
f_insrt_dt timestamptz,
f_timestamp int4 NOT NULL,
f_site_a text NOT NULL,
f_site_b text NOT NULL,
f_probe text NOT NULL,
f_ipv varchar NOT NULL,
f_lat float4,
f_los float4,
f_jit float4,
CONSTRAINT rrddata_prim_key PRIMARY KEY (f_timestamp, f_site_a, f_site_b, f_probe, f_ipv)
)
WITHOUT OIDS;
current size contents:
explain analyze select count(*) from t_rrddata
result:
Aggregate (cost=388763.80..388763.81 rows=1 width=0) (actual time=40507.054..40507.056 rows=1 loops=1)
-> Seq Scan on t_rrddata (cost=0.00..365933.04 rows=9132304 width=0) (actual time=0.081..24243.108 rows=9143881 loops=1)
Total runtime: 40507.253 ms
querying data from august 1st - august 25:
explain analyze SELECT avg(f_lat) FROM t_rrddata WHERE ((f_site_a='AD1' AND f_site_b='B1D') OR (f_site_a='B1D' AND f_site_b='AD1')) AND (f_timestamp >= '1217548800' AND f_timestamp < '1219622400')
result:
Aggregate (cost=481914.40..481914.41 rows=1 width=4) (actual time=26762.894..26762.896 rows=1 loops=1)
-> Bitmap Heap Scan on t_rrddata (cost=477853.24..481910.87 rows=1411 width=4) (actual time=26694.737..26748.520 rows=6902 loops=1)
Recheck Cond: (((f_timestamp >= 1217548800) AND (f_timestamp < 1219622400) AND (f_site_a = 'AD1'::text) AND (f_site_b = 'B1D'::text)) OR ((f_timestamp >= 1217548800) AND (f_timestamp < 1219622400) AND (f_site_a = 'B1D'::text) AND (f_site_b = 'AD1': (..)
-> BitmapOr (cost=477853.24..477853.24 rows=1411 width=0) (actual time=26686.972..26686.972 rows=0 loops=1)
-> Bitmap Index Scan on rrddata_prim_key (cost=0.00..238926.27 rows=321 width=0) (actual time=26428.925..26428.925 rows=6902 loops=1)
Index Cond: ((f_timestamp >= 1217548800) AND (f_timestamp < 1219622400) AND (f_site_a = 'AD1'::text) AND (f_site_b = 'B1D'::text))
-> Bitmap Index Scan on rrddata_prim_key (cost=0.00..238926.27 rows=1090 width=0) (actual time=258.038..258.038 rows=0 loops=1)
Index Cond: ((f_timestamp >= 1217548800) AND (f_timestamp < 1219622400) AND (f_site_a = 'B1D'::text) AND (f_site_b = 'AD1'::text))
Total runtime: 26762.999 ms
now querying data from august 1st - august 29:
explain analyze SELECT avg(f_lat) FROM t_rrddata WHERE ((f_site_a='AD1' AND f_site_b='B1D') OR (f_site_a='B1D' AND f_site_b='AD1')) AND (f_timestamp >= '1217548800' AND f_timestamp < '1220227200')
result:
Aggregate (cost=502922.09..502922.10 rows=1 width=4) (actual time=20123.474..20123.476 rows=1 loops=1)
-> Seq Scan on t_rrddata (cost=0.00..502917.60 rows=1794 width=4) (actual time=28.450..20104.788 rows=8918 loops=1)
Filter: ((((f_site_a = 'AD1'::text) AND (f_site_b = 'B1D'::text)) OR ((f_site_a = 'B1D'::text) AND (f_site_b = 'AD1'::text))) AND (f_timestamp >= 1217548800) AND (f_timestamp < 1220227200))
Total runtime: 20123.584 ms
Any idea why these analysises look so different? the only query-difference is the 2nd timestamp value, it is a little higher in the 2nd query...
let me know what other info would be of importance...
many TIA in case you take the time to check this out...
cheers
-H
