From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Rouillard <rouilj@renesys.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Assessing performance of fetches
Date: Mon, 18 Apr 2011 15:41:19 -0400
Message-ID: <15055.1303155679@sss.pgh.pa.us> (raw)
In-Reply-To: <20110418181241.GF27218@renesys.com>
References: <20110418181241.GF27218@renesys.com>
John Rouillard <rouilj@renesys.com> writes:
> I am seeing:
> 2011-04-16 00:55:33 UTC user@database(3516): LOG: duration:
> 371954.811 ms statement: FETCH FORWARD 1 FROM c_2aaaaaaeea50_a08
> While I obviously have a problem here, is there any way to log the
> actual select associated with the cursor other than logging all
> statements?
Can't think of one :-(
> Also once I have the select statement, does the fact that is is
> associated with a fetch/cursor change the steps I should take in
> tuning it compared to somebody just issuing a normal select?
The planner does treat cursor queries a bit different from plain
queries, putting more emphasis on getting the first rows sooner.
If you want to be sure you're getting the truth about what's happening,
try
EXPLAIN [ANALYZE] DECLARE c CURSOR FOR SELECT ...
rather than just
EXPLAIN [ANALYZE] SELECT ...
Other than that, it's the same as tuning a regular query.
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, rouilj@renesys.com
Subject: Re: Assessing performance of fetches
In-Reply-To: <15055.1303155679@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