Thread: Differences when calling query inside and outside cursor

Differences when calling query inside and outside cursor

From

Carlos Henrique Reimer

Date:

13 February 2013, 16:22:10

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, 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'
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 loops=3D1)
         Index Cond: ((ano > 2013::smallint) OR ((ano =3D 2013::smallint) A=
ND
(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 ((codfunc =3D 29602::bigint) AND (seqfunc =3D 2::smallint) AND
((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::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)))
 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 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;
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

Re: Differences when calling query inside and outside cursor

From

Carlos Henrique Reimer

Date:

13 February 2013, 17:04:42

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

Re: Differences when calling query inside and outside cursor

From

Albe Laurenz

Date:

13 February 2013, 17:23:49

Carlos Henrique Reimer wrote:
> We're facing a weird performance problem in one of our PostgreSQL servers=
 running 8.0.26.

Ouch.
8.0 has been out of support since October 2010, and
I am afraid that might be a problem for you.

> What can explain the difference between calling same query inside and out=
side 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.

The planner estimates that only 10% of the rows
from the cursor will be fetched.  That favors
plan that deliver the first rows quickly.

This percentage has been added as a configuration
parameter in 8.4 (cursor_tuple_fraction).
You would set this parameter to 1.0 to get the same
plans with or without cursors.

> I suspect the query called inside the cursor is using a different plan th=
an the same query outside a
> cursor. Is there a way to confirm this suspicion?

You could try
EXPLAIN DECLARE cur1 CURSOR FOR ....

Yours,
Laurenz Albe