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
