Re: Runtime variations during day

Hi,

Anyway it does not seam related to statistics as the query plan is exactly
the same for both scenarios, morning and evening:

Will include the EXPLAIN ANALYZE again here:
_______________________________________________________________________________________________

Morning:
pgipm=# explain analyze SELECT XMAX, ANO, MES, CODFUNC, SEQFUNC, TIPOPGTO,
CODPD, HRSPD, VLRPD, MESANO, TIPOCALCFERIAS, VLRBASE FROM fparq.cadpag
where (ANO >'2013') or (ANO ='2013' and MES >'01') or (ANO ='2013' and
MES ='01' and CODFUNC >'0000029602') or (ANO ='2013' and MES ='01' and
CODFUNC ='0000029602' and SEQFUNC >'02') or (ANO ='2013' and MES ='01'
and CODFUNC ='0000029602' and SEQFUNC ='02' and TIPOPGTO > (' ')) or
(ANO ='2013' and MES ='01' and CODFUNC ='0000029602' and SEQFUNC ='02'
and TIPOPGTO = (' ') and CODPD >'000') order by ANO ASC, MES ASC,
CODFUNC ASC, SEQFUNC ASC, TIPOPGTO ASC, CODPD ASC;

QUERY PLAN

------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------------
Sort (cost=151845.90..152304.21 rows=183322 width=62) (actual
time=706.676..728.080 rows=32828 loops=1)
Sort Key: ano, mes, codfunc, seqfunc, tipopgto, codpd
-> Index Scan using pagpk_aux_mes, pagpk_aux_mes, pk_cadpag, pk_cadpag,
pk_cadpag, pagchavefunc00 on cadpag (cost=0.00..131521.88 rows=183322
width=62) (actual time=0.664..614.080 rows=32828 loops=1)
Index Cond: ((ano > 2013::smallint) OR ((ano = 2013::smallint) AND
(mes > 1::smallint)) OR ((ano = 2013::smallint) AND (mes = 1::smallint) AND
(codfunc > 29602::bigint)) OR ((ano = 2013::smallint) AND (mes =
1::smallint) AND (codfunc = 29602::bigint) AND (seqfunc > 2::smallint)) OR
((ano = 2013::smallint) AND (mes = 1::smallint) AND (codfunc =
29602::bigint) AND (seqfunc = 2::smallint) AND ((tipopgto)::text > '
'::text)) OR ((codfunc = 29602::bigint) AND (seqfunc = 2::smallint) AND
((tipopgto)::text = ' '::text) AND (codpd > 0::smallint) AND (ano =
2013::smallint) AND (mes = 1::smallint)))
Filter: ((ano > 2013::smallint) OR ((ano = 2013::smallint) AND
(mes > 1::smallint)) OR ((ano = 2013::smallint) AND (mes = 1::smallint) AND
(codfunc > 29602::bigint)) OR ((ano = 2013::smallint) AND (mes =
1::smallint) AND (codfunc = 29602::bigint) AND (seqfunc > 2::smallint)) OR
((ano = 2013::smallint) AND (mes = 1::smallint) AND (codfunc =
29602::bigint) AND (seqfunc = 2::smallint) AND ((tipopgto)::text > '
'::text)) OR ((ano = 2013::smallint) AND (mes = 1::smallint) AND (codfunc =
29602::bigint) AND (seqfunc = 2::smallint) AND ((tipopgto)::text = '
'::text) AND (codpd > 0::smallint)))
Total runtime: 755.878 ms
(6 rows)
__________________________________________________________________________________________________________________________________________________________________________________________________

Evening:

explain analyze SELECT XMAX, ANO, MES, CODFUNC, SEQFUNC, TIPOPGTO, CODPD,
HRSPD, VLRPD, MESANO, TIPOCALCFERIAS, VLRBASE FROM fparq.cadpag where
(ANO >'2013') or (ANO ='2013' and MES >'01') or (ANO ='2013' and MES
='01' and CODFUNC >'0000029602') or (ANO ='2013' and MES ='01' and
CODFUNC ='0000029602' and SEQFUNC >'02') or (ANO ='2013' and MES ='01'
and CODFUNC ='0000029602' and SEQFUNC ='02' and TIPOPGTO > (' ')) or
(ANO ='2013' and MES ='01' and CODFUNC ='0000029602' and SEQFUNC ='02'
and TIPOPGTO = (' ') and CODPD >'000') order by ANO ASC, MES ASC,
CODFUNC ASC, SEQFUNC ASC, TIPOPGTO ASC, CODPD ASC;

pgipm=# explain analyze SELECT XMAX, ANO, MES, CODFUNC, SEQFUNC, TIPOPGTO,
CODPD, HRSPD, VLRPD, MESANO, TIPOCALCFERIAS, VLRBASE FROM fparq.cadpag
where (ANO >'2013') or (ANO ='2013' and MES >'01') or (ANO ='2013' and
MES ='01' and CODFUNC >'0000029602') or (ANO ='2013' and MES ='01' and
CODFUNC ='0000029602' and SEQFUNC >'02') or (ANO ='2013' and MES ='01'
and CODFUNC ='0000029602' and SEQFUNC ='02' and TIPOPGTO > (' ')) or
(ANO ='2013' and MES ='01' and CODFUNC ='0000029602' and SEQFUNC ='02'
and TIPOPGTO = (' ') and CODPD >'000') order by ANO ASC, MES ASC,
CODFUNC ASC, SEQFUNC ASC, TIPOPGTO ASC, CODPD ASC;

QUERY
PLAN

------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
--------------------------------------------------------------
Sort (cost=321670.51..322111.45 rows=176377 width=62) (actual
time=20010.616..20031.887 rows=32840 loops=1)
Sort Key: ano, mes, codfunc, seqfunc, tipopgto, codpd
-> Seq Scan on cadpag (cost=0.00..302166.75 rows=176377 width=62)
(actual time=18415.380..19915.294 rows=32840 loops=1)
Filter: ((ano > 2013::smallint) OR ((ano = 2013::smallint) AND
(mes > 1::smallint)) OR ((ano = 2013::smallint) AND (mes = 1::smallint) AND
(codfunc > 29602::bigint)) OR ((ano = 2013::smallint) AND (mes =
1::smallint) AND (codfunc = 29602::bigint) AND (seqfunc > 2::smallint)) OR
((ano = 2013::smallint) AND (mes = 1::smallint) AND (codfunc =
29602::bigint) AND (seqfunc = 2::smallint) AND ((tipopgto)::text > '
'::text)) OR ((ano = 2013::smallint) AND (mes = 1::smallint) AND (codfunc =
29602::bigint) AND (seqfunc = 2::smallint) AND ((tipopgto)::text = '
'::text) AND (codpd > 0::smallint)))
Total runtime: 20054.851 ms
(5 rows)

__________________________________________________________________________________________________________________________________________________________________________________________________

I've used this query just as an example but the general feeling is that
everything takes more time to process in the evening. Evening is also the
period with more tasks in the the database.

Another example that could help is this seqscan:

explain analyze select sittrib8 from iparq.arript where sittrib8=33;

In the evening:
Fri Feb 8 14:00:01 BRST 2013

QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on arript (cost=100000000.00..100469613.21 rows=1 width=2)
(actual time=198047.253..198047.253 rows=0 loops=1)
Filter: (sittrib8 = 33)
Total runtime: 198047.303 ms
(3 rows)

In the morning:
Fri Feb 8 10:51:01 BRST 2013
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on arript (cost=100000000.00..100469607.58 rows=1 width=2)
(actual time=11982.597..11982.597 rows=0 loops=1)
Filter: (sittrib8 = 33)
Total runtime: 11982.654 ms
(3 rows)

Thank you!

On Wed, Feb 13, 2013 at 7:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
> > Carlos Henrique Reimer <carlos(dot)reimer(at)opendb(dot)com(dot)br> wrote:
> >> I`m trying to figure out why a query runs in 755ms in the morning
> >> and 20054ms (26x) in the evening.
>
> > I would make autovacuum settings much more aggressive, or schedule
> > periodic VACUUM and/or ANALYZE runs during the day.
>
> I'm wondering about cache effects, ie memory already contains desired
> pages in the morning (perhaps as a side-effect of queries run overnight)
> and not so much by the evening. If so it's not clear that additional
> VACUUM activity would make things better.
>
> But in any case it's hard to diagnose this without EXPLAIN ANALYZE
> output.
>
> regards, tom lane
>

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos(dot)reimer(at)opendb(dot)com(dot)br