Skip site navigation (1) Skip section navigation (2)

Re: Scrollable cursors and Sort performance

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Scrollable cursors and Sort performance
Date: 2006-02-27 23:12:05
Message-ID: 20060227231205.GQ82012@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
On Mon, Feb 27, 2006 at 06:01:21PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > On Mon, Feb 27, 2006 at 02:17:23PM +0000, Simon Riggs wrote:
> >>> Possibly we could make EXPLAIN ANALYZE EXECUTE set the random-access
> >>> bits.
> >> 
> >> Good point. Whichever we do will be wrong in some cases.... I've no real
> >> opinion on this other than a vague preference for it to be quick.
> 
> > Wouldn't an EXPLAIN ANALYZE DECLARE ... have the right information to
> > know if backward scan, etc was needed?
> 
> There is no EXPLAIN ANALYZE DECLARE, and AFAICS it would be a
> contradiction in terms to have one, since DECLARE doesn't run the query.
> Perhaps the correct addition would be EXPLAIN ANALYZE FETCH.  (EXECUTE
> is unrelated, now that I think harder about it.)

You have no idea how glad I am that I'm not the only one who doesn't know about
'new' features (this first appeared in the docs in 7.4)... :)

decibel=# explain analyze declare test cursor for select * from pg_users;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on pg_authid  (cost=0.00..1.01 rows=1 width=79)
   Filter: rolcanlogin
(2 rows)

So, since presumably that accepts a full cursor declaration, would that suffice
for controlling EXPLAIN ANALYZE?

BTW, ISTM that it would also be useful to have EXPLAIN FETCH, since you
could have already defined a cursor. But I suspect a more common case
would be cut & paste of the declare from application code into psql,
which would make EXPLAIN DECLARE easier to use. Though, I never really
use cursors, so...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-02-27 23:20:36
Subject: Re: Scrollable cursors and Sort performance
Previous:From: Tom LaneDate: 2006-02-27 23:01:21
Subject: Re: Scrollable cursors and Sort performance

pgsql-patches by date

Next:From: Tom LaneDate: 2006-02-27 23:20:36
Subject: Re: Scrollable cursors and Sort performance
Previous:From: Tom LaneDate: 2006-02-27 23:01:21
Subject: Re: Scrollable cursors and Sort performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group