From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Neufeld <kneufeld@refractions.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: declared cursor uses slow plan
Date: Thu, 16 Sep 2004 14:07:43 -0400
Message-ID: <29148.1095358063@sss.pgh.pa.us> (raw)
In-Reply-To: <4148A13E.4080207@refractions.net>
References: <4148A13E.4080207@refractions.net>
Kevin Neufeld <kneufeld@refractions.net> writes:
> I have a relatively simple query that takes about 150ms using explain
> analyze. However, when I wrap the same query in a declared cursor
> statement, the subsequent fetch statement takes almost 30seconds. For
> some reason, the planner decided to do a nested loop left join instead
> of a hash left join. Does anyone know why the planner would choose this
> course?
Plans for cursors are optimized partly for startup speed as opposed to
total time, on the assumption that you'd rather get some of the rows
sooner so you can crunch on them.
Probably there should be a knob you can fool with to adjust the strength
of the effect, but at present I think it's hard-wired.
The real problem here of course is that the total cost of the nestloop
is being underestimated so badly (the estimate is only 5x more than the
hash join where reality is 200x more). It looks like this is mainly
because the number of matching rows from csn_waterbodies is badly
underestimated, which comes from the fact that we have no useful
statistics for geometric operators :-(. I think that the PostGIS crew
is working that problem but I have no idea how far along they are...
regards, tom lane
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: tgl@sss.pgh.pa.us, kneufeld@refractions.net
Subject: Re: declared cursor uses slow plan
In-Reply-To: <29148.1095358063@sss.pgh.pa.us>
* 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