Re: Runtime variations during day

Some issues are just funny.
Maybe lots of inserts deletes during the day?
Vacuum/analyze timing may have an impact on the planner?
Try again morning evening with vac/ana commands before the query.

Op 13-2-2013 19:42, Carlos Henrique Reimer schreef:
>
> Hi,
>
> I`m trying to figure out why a query runs in 755ms in the morning and
> 20054ms (26x) in the evening.
>
> _________________________________________________________________________________________________________________________________________________________________________________________
>
> 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)
>
>
> __________________________________________________________________________________________________________________________________________________________________________________________________
>
>
> We initially suspected the reason could be that in the morning all
> data is in memory and in the evening not all is in memory but as
> database size is 40GB and memory 64GB I would eliminate this
> hypothesis . Another reason we rejected this hypothesis is that even
> if you run the query two times, both took almost the same time.
>
> Another possibility is a CPU bottleneck but as there is no indication
> of this condition in the performance data collected by sar, top,
> vmstat we assume the problem has another origin.
>
> How could we determine why this difference in the response time?
>
> Thank you in advance!
>
> Reimer
>