plpgsql vs. SQL performance (again)

From: Michael Pohl <pgsql(at)newtopia(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: plpgsql vs. SQL performance (again)
Date: 2003-07-10 01:48:36
Message-ID: Pine.LNX.4.44.0307092104480.6784-100000@server1.techplant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance pgsql-sql

About a month ago I asked the general list about plpgsql functions that
occasionally significantly underperform their straight SQL equivalents.
Tom noted that a different query plan was almost certainly being chosen by
the plpgsql function:

http://archives.postgresql.org/pgsql-general/2003-05/msg00966.php
http://archives.postgresql.org/pgsql-general/2003-05/msg00998.php

Tom suggested checking for sloppy datatype declarations in the plpgsql
functions. Double-checked, a-ok.

Tom also suggested that indexscans might not get picked by the plpgsql
function if I have some very skewed statistics. Is there a way to verify
the plpgsql function's planner choices?

My casual observations are that this problem occurs with aggregates, and
that the big performance hit is not consistent. I'd like advice on more
formal troubleshooting.

I can provide examples (my latest problem function is currently taking
over 4 seconds vs. .04 seconds for its straight SQL equivalent), table
schema, explain output for the straight SQL, etc., if anyone cares to work
through this with me.

thanks,

michael

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Chris_Wu 2003-07-10 01:51:53 Can you help me?
Previous Message Martin Foster 2003-07-10 00:23:44 Re: Moving postgresql.conf tunables into 2003...

Browse pgsql-sql by date

  From Date Subject
Next Message Raj Mathur 2003-07-10 03:36:01 Re: Home-brewed table syncronization
Previous Message Dmitry Tkach 2003-07-09 22:51:48 Re: Datatype conversion help