Re: Runtime variations during day - Mailing list pgsql-general

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
>