Runtime variations during day

[Carlos Henrique Reimer <carlos.reimer@xxxxxxxxxxxxx>]

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