Thread: weird query runtime results...

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=6902loops=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.925rows=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.038rows=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
higherin 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

On 2008-09-09 05:07, Kashmir wrote:

> querying data from august 1st - august 25:
>  Total runtime: 26762.999 ms
>
> now querying data from august 1st - august 29:
>  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...

Did you realize that this second query returns over 6 seconds faster
than first?

If you query a significant portion of data a table scan is faster than
using indexes as a drive does not need to seek that much.

You can control when it will switch to seq-scan tuning
seq_page_cost/random_page_cost parameters in postgresql.conf. But test
also other queries common for your usage.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh