Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 6289455C5A4 for ; Mon, 16 Jul 2012 11:44:27 -0300 (ADT) Received: from mail-gg0-f174.google.com ([209.85.161.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SqmX6-00080T-4O for pgsql-performance@postgresql.org; Mon, 16 Jul 2012 14:44:26 +0000 Received: by gglu4 with SMTP id u4so5053396ggl.19 for ; Mon, 16 Jul 2012 07:44:03 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:x-gm-message-state; bh=lIXMM8W7/b3/GqtGK8B1cTFMwU93FJ4/Yp30Anz1wEg=; b=BJ0ETkG7eUSK2/Cf0hcNT2h7m7fBYI+RYZl0Dlz3cTc3j5IwPqYWzsqTtUcWPymIec F4OLu00dISFWVDxLFv8u7Hzu+ES++xyeHKt+PHhcOd/2rOJSpNz6elf3r+zZiEgtFFNY 1QWj1UgNRtH0BCwqsTUXeSu1FC2C1JBe5GVMKkMCU59l7bL++Rv5qCuFe0x71bz0N41m k5/SW0tDJWBzzyb0F3BMeaBe2RorTRuEFoTlqFL/Pxa5klZ3BveKnmFYgtw6guPSl71W 9TCt37xNm5a/vm6yPFSzoYThkG2omMBmZgudO4hp8yJtUTuQ3ey5kuf8Lte2yIARNDTY 8MvQ== Received: by 10.66.76.170 with SMTP id l10mr23147187paw.57.1342449843297; Mon, 16 Jul 2012 07:44:03 -0700 (PDT) Received: from ayaki.localdomain (58-7-231-93.dyn.iinet.net.au. [58.7.231.93]) by mx.google.com with ESMTPS id pc6sm12017354pbc.47.2012.07.16.07.44.00 (version=SSLv3 cipher=OTHER); Mon, 16 Jul 2012 07:44:02 -0700 (PDT) Message-ID: <500428AD.5060400@ringerc.id.au> Date: Mon, 16 Jul 2012 22:43:57 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:13.0) Gecko/20120605 Thunderbird/13.0 MIME-Version: 1.0 To: Andy Halsall CC: pgsql-performance@postgresql.org Subject: Re: query overhead References: , <5000E75A.6030108@ringerc.id.au> In-Reply-To: Content-Type: multipart/alternative; boundary="------------060604030802050801000005" X-Gm-Message-State: ALoCoQkCpbk/O2FX8PgFJ6QfyRAvEDiPLed2lvt/dRHvnT4PgwYpFDAZ9FeOkTPdbvATekh7o76r X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/194 X-Sequence-Number: 47375 This is a multi-part message in MIME format. --------------060604030802050801000005 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 07/16/2012 06:13 PM, Andy Halsall wrote: > Thanks for the responses. I take the points - the times we're dealing > with are very small. Sorry but I'm a bit confused by the suggestions > around function types / prepared statements, but probably haven't been > clear in my question: I'm invoking a PL/PgSQL function from libpq, for > example the no_op mentioned in first post does: > > CREATE OR REPLACE FUNCTION sp_select_no_op() RETURNS integer AS > ' > begin > return 1; > end > ' > language 'plpgsql' IMMUTABLE; > > My understanding was that the plan for this would be prepared once and > reused. So no addvantage in a prepared statement? Also no advantage in > making this a plain SQL function as these don't get cached? AFAIK SQL functions don't get cached plans - though I'm not 100% on this. They can be lots cheaper for wrapping simple operations, though. I'm just questioning why you're going immediately to PL/PgSQL - or stored procs at all. It might be a bigger hammer than you need. What sorts of operations will your application be performing? Is there any reason it can't directly use simple INSERT, UPDATE, DELETE and SELECT statements, possibly with PREPARE and EXECUTE at libpq level? If you're trying to go as fast as humanly possible in emulating an ISAM-like access model with lots of small fast accesses, PQprepare of simple S/I/U/D statements, then proper use of PQexecPrepared, is likely to be hard to beat. If you're working with ISAM-like access though, cursors may well be very helpful for you. It's a pity for your app that Pg doesn't support cursors that see changes committed after cursor creation, since these are ideal when emulating ISAM "next record" / "previous record" access models. They're still suitable for tasks where you know the app doesn't need to see concurrently modified data, though. Can you show a typical sequence of operations for your DB? Also, out of interest, are you migrating from a traditional shared-file ISAM-derived database system, or something funkier? > > Embedded database such as SQLLite is a good idea except that we'll be > multi-process and my understanding is that they lock the full database > on any write, which is off-putting. Write concurrency in SQLite is miserable, yeah, but you get very fast shared-access reads as a trade-off and it's much closer to your app's old DB design. It depends a lot on your workload. -- Craig Ringer --------------060604030802050801000005 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 07/16/2012 06:13 PM, Andy Halsall wrote:
Thanks for the responses. I take the points - the times we're dealing with are very small. Sorry but I'm a bit confused by the suggestions around function types / prepared statements, but probably haven't been clear in my question: I'm invoking a PL/PgSQL function from libpq, for example the no_op mentioned in first post does:
 
    CREATE OR REPLACE FUNCTION sp_select_no_op() RETURNS integer AS
    '
    begin
         return 1;
    end
    '
    language 'plpgsql' IMMUTABLE;
 
My understanding was that the plan for this would be prepared once and reused. So no addvantage in a prepared statement? Also no advantage in making this a plain SQL function as these don't get cached?
AFAIK SQL functions don't get cached plans - though I'm not 100% on this. They can be lots cheaper for wrapping simple operations, though.

I'm just questioning why you're going immediately to PL/PgSQL - or stored procs at all. It might be a bigger hammer than you need.

What sorts of operations will your application be performing? Is there any reason it can't directly use simple INSERT, UPDATE, DELETE and SELECT statements, possibly with PREPARE and EXECUTE at libpq level?

If you're trying to go as fast as humanly possible in emulating an ISAM-like access model with lots of small fast accesses, PQprepare of simple S/I/U/D statements, then proper use of PQexecPrepared, is likely to be hard to beat.

If you're working with ISAM-like access though, cursors may well be very helpful for you. It's a pity for your app that Pg doesn't support cursors that see changes committed after cursor creation, since these are ideal when emulating ISAM "next record" / "previous record" access models. They're still suitable for tasks where you know the app doesn't need to see concurrently modified data, though.

Can you show a typical sequence of operations for your DB?

Also, out of interest, are you migrating from a traditional shared-file ISAM-derived database system, or something funkier?

 
Embedded database such as SQLLite is a good idea except that we'll be multi-process and my understanding is that they lock the full database on any write, which is off-putting.

Write concurrency in SQLite is miserable, yeah, but you get very fast shared-access reads as a trade-off and it's much closer to your app's old DB design. It depends a lot on your workload.

--
Craig Ringer

--------------060604030802050801000005--