From: John A Meinel <john@arbash-meinel.com>
To: Niccolo Rigacci <niccolo@rigacci.org>
To: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: CURSOR slowes down a WHERE clause 100 times?
Date: Wed, 06 Jul 2005 16:29:00 -0500
Message-ID: <42CC4D1C.9040606@arbash-meinel.com> (raw)
In-Reply-To: <20050706211946.GA3460@rigacci.org>
References: <20050706211946.GA3460@rigacci.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, niccolo@rigacci.org
Subject: Re: CURSOR slowes down a WHERE clause 100 times?
In-Reply-To: <42CC4D1C.9040606@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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