From: Michael Pohl <pgsql@newtopia.com>
To: pgsql-performance@postgresql.org
Subject: plpgsql vs. SQL performance (again)
Date: Wed, 9 Jul 2003 21:48:36 -0400 (EDT)
Message-ID: <Pine.LNX.4.44.0307092104480.6784-100000@server1.techplant.com> (raw)
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.phphttp://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
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-sql@postgresql.org
Cc: pgsql@newtopia.com, pgsql-performance@postgresql.org
Subject: Re: plpgsql vs. SQL performance (again)
In-Reply-To: <Pine.LNX.4.44.0307092104480.6784-100000@server1.techplant.com>
* 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