Anyway it does not seam related to statistics as the query plan is exactly the same for both scenarios, morning and evening:
Will include the EXPLAIN ANALYZE again here:
_______________________________________________________________________________________________
Morning:
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)
__________________________________________________________________________________________________________________________________________________________________________________________________
I've used this query just as an example but the general feeling is that everything takes more time to process in the evening. Evening is also the period with more tasks in the the database.
Another example that could help is this seqscan:
explain analyze select sittrib8 from iparq.arript where sittrib8=33;
In the evening:
Fri Feb 8 14:00:01 BRST 2013
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on arript (cost=100000000.00..100469613.21 rows=1 width=2) (actual time=198047.253..198047.253 rows=0 loops=1)
Filter: (sittrib8 = 33)
Total runtime: 198047.303 ms
(3 rows)
In the morning:
Fri Feb 8 10:51:01 BRST 2013
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on arript (cost=100000000.00..100469607.58 rows=1 width=2) (actual time=11982.597..11982.597 rows=0 loops=1)
Filter: (sittrib8 = 33)
Total runtime: 11982.654 ms
(3 rows)
Thank you!
Kevin Grittner <kgrittn@xxxxxxxxx> writes:I'm wondering about cache effects, ie memory already contains desired
> Carlos Henrique Reimer <carlos.reimer@xxxxxxxxxxxxx> wrote:
>> I`m trying to figure out why a query runs in 755ms in the morning
>> and 20054ms (26x) in the evening.
> I would make autovacuum settings much more aggressive, or schedule
> periodic VACUUM and/or ANALYZE runs during the day.
pages in the morning (perhaps as a side-effect of queries run overnight)
and not so much by the evening. If so it's not clear that additional
VACUUM activity would make things better.
But in any case it's hard to diagnose this without EXPLAIN ANALYZE
output.
regards, tom lane
--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@xxxxxxxxxxxxx
