plpgsql vs. SQL performance (again) - Mailing list pgsql-performance

From Michael Pohl
Subject plpgsql vs. SQL performance (again)
Date
Msg-id Pine.LNX.4.44.0307092104480.6784-100000@server1.techplant.com
Whole thread Raw
List pgsql-performance
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


pgsql-performance by date:

Previous
From: "Chris_Wu"
Date:
Subject: Can you help me?
Next
From: Martin Foster
Date:
Subject: Re: [NOVICE] Extreme high load averages