Runtime variations during day - Mailing list pgsql-general

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