|
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
|
