Differences when calling query inside and outside cursor

Hi,

As we suspected the SELECT inside a cursor is using a different plan than
outside a cursor:

pgipm=# 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 ='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

------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
--------------------------------------------------------
Index Scan using pk_cadpag on cadpag (cost=0.00..383580.89 rows=183326
width=62)
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)))
(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=# 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)

________________________________________________________________________________________________________________________________________________
Query called inside a cursor:
pgipm=# select current_time;
timetz
--------------------
10:51:39.747798-02
(1 row)

pgipm=# BEGIN WORK;
BEGIN
pgipm=# 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 ='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;
DECLARE CURSOR
pgipm=# 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=# select current_time;
timetz
--------------------
10:51:39.748351-02
(1 row)

pgipm=# rollback;
ROLLBACK
pgipm=# select current_time;
timetz
--------------------
10:52:13.202640-02
(1 row)

pgipm=#

Thank you!

Reimer

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general