Re: Differences when calling query inside and outside cursor
От | Carlos Henrique Reimer |
---|---|
Тема | Re: Differences when calling query inside and outside cursor |
Дата | |
Msg-id | CAJnnue0RM4ZMOp+zkTnjHD6T6teyf2JZJswTQY0j3Yh0o4br9g@mail.gmail.com обсуждение исходный текст |
Ответ на | Differences when calling query inside and outside cursor (Carlos Henrique Reimer <carlos.reimer@opendb.com.br>) |
Список | pgsql-general |
Hi, As we suspected the SELECT inside a cursor is using a different plan than outside a cursor: pgipm=3D# explain analyze DECLARE CUR1 CURSOR FOR pgipm-# SELECT XMAX, ANO, MES, CODFUNC, SEQFUNC, TIPOPGTO, CODPD, HRSPD, VLRPD, MESANO, TIPOCALCFERIAS, VLRBASE FROM fparq.cadpag where (ANO >'2013') or (ANO =3D'2013' and MES >'01') or (ANO =3D'2013' and MES =3D= '01' and CODFUNC >'0000029602') or (ANO =3D'2013' and MES =3D'01' and CODFUNC =3D'0000029602' and SEQFUNC >'02') or (ANO =3D'2013' and MES =3D'01' an= d CODFUNC =3D'0000029602' and SEQFUNC =3D'02' and TIPOPGTO > (' ')) or (= ANO =3D'2013' and MES =3D'01' and CODFUNC =3D'0000029602' and SEQFUNC =3D'02= ' and TIPOPGTO =3D (' ') and CODPD >'000') order by ANO ASC, MES ASC, CODFUN= C ASC, SEQFUNC ASC, TIPOPGTO ASC, CODPD ASC; QUERY PLAN ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ -------------------------------------------------------- Index Scan using pk_cadpag on cadpag (cost=3D0.00..383580.89 rows=3D18332= 6 width=3D62) Filter: ((ano > 2013::smallint) OR ((ano =3D 2013::smallint) AND (mes > 1::smallint)) OR ((ano =3D 2013::smallint) AND (mes =3D 1::smallint) AND (codfunc > 29602::bigint)) OR ((ano =3D 2013::smallint) AND (mes =3D 1::smallint) AND (codfunc =3D 29602::bigint) AND (seqfunc > 2::smallint)) O= R ((ano =3D 2013::smallint) AND (mes =3D 1::smallint) AND (codfunc =3D 29602::bigint) AND (seqfunc =3D 2::smallint) AND ((tipopgto)::text > ' '::text)) OR ((ano =3D 2013::smallint) AND (mes =3D 1::smallint) AND (codfu= nc =3D 29602::bigint) AND (seqfunc =3D 2::smallint) AND ((tipopgto)::text =3D ' '::text) AND (codpd > 0::smallint))) (2 rows) Should it not be the same inside or outside a cursor? Thank you in advance! On Wed, Feb 13, 2013 at 11:21 AM, Carlos Henrique Reimer < carlos.reimer@opendb.com.br> wrote: > Hi, > > We're facing a weird performance problem in one of our PostgreSQL servers > running 8.0.26. > > What can explain the difference between calling same query inside and > outside a cursor? If we run the query outside a cursor we got a response > time of 755ms and 33454ms if we call the same query inside a cursor. > > I suspect the query called inside the cursor is using a different plan > than the same query outside a cursor. Is there a way to confirm this > suspicion? > > > Query called outside a cursor: > pgipm=3D# explain analyze SELECT XMAX, ANO, MES, CODFUNC, SEQFUNC, TIPOPG= TO, > CODPD, HRSPD, VLRPD, MESANO, TIPOCALCFERIAS, VLRBASE FROM fparq.cadpag > where (ANO >'2013') or (ANO =3D'2013' and MES >'01') or (ANO =3D'2013= ' and > MES =3D'01' and CODFUNC >'0000029602') or (ANO =3D'2013' and MES =3D'0= 1' and > CODFUNC =3D'0000029602' and SEQFUNC >'02') or (ANO =3D'2013' and MES = =3D'01' > and CODFUNC =3D'0000029602' and SEQFUNC =3D'02' and TIPOPGTO > (' '))= or > (ANO =3D'2013' and MES =3D'01' and CODFUNC =3D'0000029602' and SEQFUNC= =3D'02' > and TIPOPGTO =3D (' ') and CODPD >'000') order by ANO ASC, MES ASC, > CODFUNC ASC, SEQFUNC ASC, TIPOPGTO ASC, CODPD ASC; > > QUERY > PLAN > > > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------- > Sort (cost=3D151845.90..152304.21 rows=3D183322 width=3D62) (actual > time=3D706.676..728.080 rows=3D32828 loops=3D1) > 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=3D0.00..131521.88 > rows=3D183322 width=3D62) (actual time=3D0.664..614.080 rows=3D32828 loop= s=3D1) > Index Cond: ((ano > 2013::smallint) OR ((ano =3D 2013::smallint) > AND (mes > 1::smallint)) OR ((ano =3D 2013::smallint) AND (mes =3D 1::sma= llint) > AND (codfunc > 29602::bigint)) OR ((ano =3D 2013::smallint) AND (mes =3D > 1::smallint) AND (codfunc =3D 29602::bigint) AND (seqfunc > 2::smallint))= OR > ((ano =3D 2013::smallint) AND (mes =3D 1::smallint) AND (codfunc =3D > 29602::bigint) AND (seqfunc =3D 2::smallint) AND ((tipopgto)::text > ' > '::text)) OR ((codfunc =3D 29602::bigint) AND (seqfunc =3D 2::smallint) A= ND > ((tipopgto)::text =3D ' '::text) AND (codpd > 0::smallint) AND (ano =3D > 2013::smallint) AND (mes =3D 1::smallint))) > Filter: ((ano > 2013::smallint) OR ((ano =3D 2013::smallint) AND > (mes > 1::smallint)) OR ((ano =3D 2013::smallint) AND (mes =3D 1::smallin= t) AND > (codfunc > 29602::bigint)) OR ((ano =3D 2013::smallint) AND (mes =3D > 1::smallint) AND (codfunc =3D 29602::bigint) AND (seqfunc > 2::smallint))= OR > ((ano =3D 2013::smallint) AND (mes =3D 1::smallint) AND (codfunc =3D > 29602::bigint) AND (seqfunc =3D 2::smallint) AND ((tipopgto)::text > ' > '::text)) OR ((ano =3D 2013::smallint) AND (mes =3D 1::smallint) AND (cod= func =3D > 29602::bigint) AND (seqfunc =3D 2::smallint) AND ((tipopgto)::text =3D ' > '::text) AND (codpd > 0::smallint))) > Total runtime: 755.878 ms > (6 rows) > > _________________________________________________________________________= _______________________________________________________________________ > Query called inside a cursor: > pgipm=3D# select current_time; > timetz > -------------------- > 10:51:39.747798-02 > (1 row) > > pgipm=3D# BEGIN WORK; > BEGIN > pgipm=3D# DECLARE CUR1 CURSOR FOR > pgipm-# SELECT XMAX, ANO, MES, CODFUNC, SEQFUNC, TIPOPGTO, CODPD, HRSPD, > VLRPD, MESANO, TIPOCALCFERIAS, VLRBASE FROM fparq.cadpag where (ANO > >'2013') or (ANO =3D'2013' and MES >'01') or (ANO =3D'2013' and MES = =3D'01' > and CODFUNC >'0000029602') or (ANO =3D'2013' and MES =3D'01' and CODFU= NC > =3D'0000029602' and SEQFUNC >'02') or (ANO =3D'2013' and MES =3D'01' = and > CODFUNC =3D'0000029602' and SEQFUNC =3D'02' and TIPOPGTO > (' ')) or= (ANO > =3D'2013' and MES =3D'01' and CODFUNC =3D'0000029602' and SEQFUNC =3D'= 02' and > TIPOPGTO =3D (' ') and CODPD >'000') order by ANO ASC, MES ASC, CODF= UNC > ASC, SEQFUNC ASC, TIPOPGTO ASC, CODPD ASC; > DECLARE CURSOR > pgipm=3D# FETCH FORWARD 2 FROM CUR1; > xmax | ano | mes | codfunc | seqfunc | tipopgto | codpd | hrspd | > vlrpd | mesano | tipocalcferias | vlrbase > > ------+------+-----+---------+---------+----------+-------+--------+-----= ----+--------+----------------+--------- > 0 | 2013 | 1 | 29602 | 2 | R | 0 | 220.00 | > 1743.28 | 12013 | | 0.00 > 0 | 2013 | 1 | 29602 | 2 | R | 53 | 14.67 | > 116.22 | 12013 | | 0.00 > (2 rows) > > pgipm=3D# select current_time; > timetz > -------------------- > 10:51:39.748351-02 > (1 row) > > pgipm=3D# rollback; > ROLLBACK > pgipm=3D# select current_time; > timetz > -------------------- > 10:52:13.202640-02 > (1 row) > > pgipm=3D# > > Thank you! > > Reimer > > --=20 Reimer 47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br
В списке pgsql-general по дате отправления:
Предыдущее
От: Carlos Henrique ReimerДата:
Сообщение: Differences when calling query inside and outside cursor
Следующее
От: "Gauthier, Dave"Дата:
Сообщение: How to get stored procedure args list from metadata tables ?