pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCURSOR slowes down a WHERE clause 100 times?
7+ messages / 4 participants
[nested] [flat]
* CURSOR slowes down a WHERE clause 100 times?
@ 2005-07-06 21:19 Niccolo Rigacci <niccolo@rigacci.org>
0 siblings, 2 replies; 7+ messages in thread
From: Niccolo Rigacci @ 2005-07-06 21:19 UTC (permalink / raw)
To: pgsql-performance
Hi to all,
I have a performace problem with the following query:
BEGIN;
DECLARE mycursor BINARY CURSOR FOR
SELECT
toponimo,
wpt
FROM wpt_comuni_view
WHERE (
wpt &&
setSRID('BOX3D(4.83 36, 20.16 47.5)'::BOX3D, 4326)
);
FETCH ALL IN mycursor;
END;
I get the results in about 108 seconds (8060 rows).
If I issue the SELECT alone (without the CURSOR) I get the
same results in less than 1 second.
The wpt_comuni_view is a VIEW of a 3 tables JOIN, and the "wpt"
field is a PostGIS geometry column. The "&&" is the PostGIS
"overlaps" operator.
If I CURSOR SELECT from a temp table instead of the JOIN VIEW the
query time 1 second.
If I omit the WHERE clause the CURSOR fetches results in 1
second.
Can the CURSOR on JOIN affects so heavly the WHERE clause? I
suspect that - with the CURSOR - a sequential scan is performed
on the entire data set for each fetched record...
Any idea?
This is the definition of the VIEW:
CREATE VIEW wpt_comuni_view AS
SELECT istat_wpt.oid, istat_wpt.id, istat_wpt.toponimo,
istat_comuni.residenti, istat_wpt.wpt
FROM istat_comuni
JOIN istat_comuni2wpt
USING (idprovincia, idcomune)
JOIN istat_wpt
ON (idwpt = id);
Thank you for any hint.
--
Niccolo Rigacci
Firenze - Italy
War against Iraq? Not in my name!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CURSOR slowes down a WHERE clause 100 times?
@ 2005-07-06 21:29 John A Meinel <john@arbash-meinel.com>
parent: Niccolo Rigacci <niccolo@rigacci.org>
1 sibling, 1 reply; 7+ messages in thread
From: John A Meinel @ 2005-07-06 21:29 UTC (permalink / raw)
To: Niccolo Rigacci <niccolo@rigacci.org>; pgsql-performance
Niccolo Rigacci wrote:
>Hi to all,
>
>I have a performace problem with the following query:
>
> BEGIN;
> DECLARE mycursor BINARY CURSOR FOR
> SELECT
> toponimo,
> wpt
> FROM wpt_comuni_view
> WHERE (
> wpt &&
> setSRID('BOX3D(4.83 36, 20.16 47.5)'::BOX3D, 4326)
> );
> FETCH ALL IN mycursor;
> END;
>
>I get the results in about 108 seconds (8060 rows).
>
>If I issue the SELECT alone (without the CURSOR) I get the
>same results in less than 1 second.
>
>The wpt_comuni_view is a VIEW of a 3 tables JOIN, and the "wpt"
>field is a PostGIS geometry column. The "&&" is the PostGIS
>"overlaps" operator.
>
>If I CURSOR SELECT from a temp table instead of the JOIN VIEW the
>query time 1 second.
>
>If I omit the WHERE clause the CURSOR fetches results in 1
>second.
>
>Can the CURSOR on JOIN affects so heavly the WHERE clause? I
>suspect that - with the CURSOR - a sequential scan is performed
>on the entire data set for each fetched record...
>
>Any idea?
>
>
What does it say if you do "EXPLAIN ANALYZE SELECT..." both with and
without the cursor?
It may not say much for the cursor, but I think you can explain analyze
the fetch statements.
It is my understanding that Cursors generally favor using an
slow-startup style plan, which usually means using an index, because it
expects that you won't actually want all of the data. A seqscan is not
always slower, especially if you need to go through most of the data.
Without an explain analyze it's hard to say what the planner is thinking
and doing.
>This is the definition of the VIEW:
>
> CREATE VIEW wpt_comuni_view AS
> SELECT istat_wpt.oid, istat_wpt.id, istat_wpt.toponimo,
> istat_comuni.residenti, istat_wpt.wpt
> FROM istat_comuni
> JOIN istat_comuni2wpt
> USING (idprovincia, idcomune)
> JOIN istat_wpt
> ON (idwpt = id);
>
>Thank you for any hint.
>
>
>
You might also try comparing your CURSOR to a prepared statement. There
are a few rare cases where preparing is worse than issuing the query
directly, depending on your data layout.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CURSOR slowes down a WHERE clause 100 times?
@ 2005-07-07 07:41 Niccolo Rigacci <niccolo@rigacci.org>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 7+ messages in thread
From: Niccolo Rigacci @ 2005-07-07 07:41 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
> >Can the CURSOR on JOIN affects so heavly the WHERE clause? I
> >suspect that - with the CURSOR - a sequential scan is performed
> >on the entire data set for each fetched record...
> >
> >Any idea?
> >
> >
> What does it say if you do "EXPLAIN ANALYZE SELECT..." both with and
> without the cursor?
> It may not say much for the cursor, but I think you can explain analyze
> the fetch statements.
How can I EXPLAIN ANALYZE a cursor like this?
BEGIN;
DECLARE mycursor BINARY CURSOR FOR
SELECT ...
FETCH ALL IN mycursor;
END;
I tried to put EXPLAIN ANALYZE in front of the SELECT and in
front of the FETCH, but I got two "syntax error"...
Thanks
--
Niccolo Rigacci
Firenze - Italy
War against Iraq? Not in my name!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CURSOR slowes down a WHERE clause 100 times?
@ 2005-07-07 08:13 Niccolo Rigacci <niccolo@rigacci.org>
parent: Niccolo Rigacci <niccolo@rigacci.org>
1 sibling, 1 reply; 7+ messages in thread
From: Niccolo Rigacci @ 2005-07-07 08:13 UTC (permalink / raw)
To: pgsql-performance
On Wed, Jul 06, 2005 at 11:19:46PM +0200, Niccolo Rigacci wrote:
>
> I have a performace problem with the following query:
>
> BEGIN;
> DECLARE mycursor BINARY CURSOR FOR
> SELECT
> toponimo,
> wpt
> FROM wpt_comuni_view
> WHERE (
> wpt &&
> setSRID('BOX3D(4.83 36, 20.16 47.5)'::BOX3D, 4326)
> );
> FETCH ALL IN mycursor;
> END;
>
> I get the results in about 108 seconds (8060 rows).
>
> If I issue the SELECT alone (without the CURSOR) I get the
> same results in less than 1 second.
By trial and error I discovered that adding an "ORDER BY
toponimo" clause to the SELECT, boosts the CURSOR performances
so that they are now equiparable to the SELECT alone.
Is there some documentation on how an ORDER can affect the
CURSOR in a different way than the SELECT?
--
Niccolo Rigacci
Firenze - Italy
War against Iraq? Not in my name!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CURSOR slowes down a WHERE clause 100 times?
@ 2005-07-07 09:14 Richard Huxton <dev@archonet.com>
parent: Niccolo Rigacci <niccolo@rigacci.org>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2005-07-07 09:14 UTC (permalink / raw)
To: Niccolo Rigacci <niccolo@rigacci.org>; +Cc: pgsql-performance
Niccolo Rigacci wrote:
>>
>>I get the results in about 108 seconds (8060 rows).
>>
>>If I issue the SELECT alone (without the CURSOR) I get the
>>same results in less than 1 second.
>
>
> By trial and error I discovered that adding an "ORDER BY
> toponimo" clause to the SELECT, boosts the CURSOR performances
> so that they are now equiparable to the SELECT alone.
>
> Is there some documentation on how an ORDER can affect the
> CURSOR in a different way than the SELECT?
I think you're misunderstanding exactly what's happening here. If you
ask for a cursor, PG assumes you aren't going to want all the results
(or at least not straight away). After all, most people use them to work
through results in comparatively small chunks, perhaps only ever
fetching 1% of the total results.
So - if you ask for a cursor, PG weights things to give you the first
few rows as soon as possible, at the expense of fetching *all* rows
quickly. If you're only going to fetch e.g. the first 20 rows this is
exactly what you want. In your case, since you're immediately issuing
FETCH ALL, you're not really using the cursor at all, but PG doesn't
know that.
So - the ORDER BY means PG has to sort all the results before returning
the first row anyway. That probably means the plans with/without cursor
are identical.
Of course, all this assumes that your configuration settings are good
and statistics adequate. To test that, try fetching just the first row
from your cursor with/without the ORDER BY. Without should be quicker.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CURSOR slowes down a WHERE clause 100 times?
@ 2005-07-07 10:06 Niccolo Rigacci <niccolo@rigacci.org>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 7+ messages in thread
From: Niccolo Rigacci @ 2005-07-07 10:06 UTC (permalink / raw)
To: pgsql-performance
On Thu, Jul 07, 2005 at 10:14:50AM +0100, Richard Huxton wrote:
> >By trial and error I discovered that adding an "ORDER BY
> >toponimo" clause to the SELECT, boosts the CURSOR performances
> >so that they are now equiparable to the SELECT alone.
> I think you're misunderstanding exactly what's happening here. If you
> ask for a cursor, PG assumes you aren't going to want all the results
> (or at least not straight away). After all, most people use them to work
> through results in comparatively small chunks, perhaps only ever
> fetching 1% of the total results.
This make finally sense!
> In your case, since you're immediately issuing FETCH ALL,
> you're not really using the cursor at all, but PG doesn't know
> that.
In fact, fetching only the first rows from the cursor, is rather
quick! This demonstrates that the PG planner is smart.
Not so smart are the MapServer and QGIS query builders, which use
a CURSOR to FETCH ALL.
I will investigate in this direction now.
Thank you very much, your help was excellent!
--
Niccolo Rigacci
Firenze - Italy
War against Iraq? Not in my name!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CURSOR slowes down a WHERE clause 100 times?
@ 2005-07-07 13:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Niccolo Rigacci <niccolo@rigacci.org>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-07-07 13:59 UTC (permalink / raw)
To: Niccolo Rigacci <niccolo@rigacci.org>; +Cc: John A Meinel <john@arbash-meinel.com>; pgsql-performance
Niccolo Rigacci <niccolo@rigacci.org> writes:
> How can I EXPLAIN ANALYZE a cursor like this?
> BEGIN;
> DECLARE mycursor BINARY CURSOR FOR
> SELECT ...
> FETCH ALL IN mycursor;
> END;
> I tried to put EXPLAIN ANALYZE in front of the SELECT and in
> front of the FETCH, but I got two "syntax error"...
Just FYI, you can't EXPLAIN ANALYZE this, but you can EXPLAIN it:
EXPLAIN DECLARE x CURSOR FOR ...
so you can at least find out what the plan is.
It might be cool to support EXPLAIN ANALYZE FETCH --- not sure what that
would take.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-07-07 13:59 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-06 21:19 CURSOR slowes down a WHERE clause 100 times? Niccolo Rigacci <niccolo@rigacci.org>
2005-07-06 21:29 ` John A Meinel <john@arbash-meinel.com>
2005-07-07 07:41 ` Niccolo Rigacci <niccolo@rigacci.org>
2005-07-07 13:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-07 08:13 ` Niccolo Rigacci <niccolo@rigacci.org>
2005-07-07 09:14 ` Richard Huxton <dev@archonet.com>
2005-07-07 10:06 ` Niccolo Rigacci <niccolo@rigacci.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox