pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction performs differently with different values
2+ messages / 2 participants
[nested] [flat]
* function performs differently with different values
@ 2010-04-10 20:47 Ben Chobot <bench@silentmedia.com>
2010-04-12 13:21 ` Re: function performs differently with different values Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Ben Chobot @ 2010-04-10 20:47 UTC (permalink / raw)
To: pgsql-performance
I've got a sql language function which does a fairly simple select from a table. If I give it one value, it performs quickly (half a ms). If I give it another value, it does not (1.1 seconds). When I run the equivalent select outside of the function, both values perform roughly the same (even though one value returns 140k more rows, as expected).
My understanding is that this generally happens because the plan should be different for the different values, but the first time the function is run it caches the plan for one of the values and will never use the appropriate plan for the second value. However, when I do an explain analyze of the raw sql for both values, I get the same plan. So my understanding must be wrong?
I suppose the other possibility is that the slower value is slower in a function because it's returning 140k more rows and the function has to deal with that additional data..... but that seems far-fetched, given that each row is just an int.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: function performs differently with different values
2010-04-10 20:47 function performs differently with different values Ben Chobot <bench@silentmedia.com>
@ 2010-04-12 13:21 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Robert Haas @ 2010-04-12 13:21 UTC (permalink / raw)
To: Ben Chobot <bench@silentmedia.com>; +Cc: pgsql-performance
On Sat, Apr 10, 2010 at 4:47 PM, Ben Chobot <bench@silentmedia.com> wrote:
> My understanding is that this generally happens because the plan should be different for the different values, but the first time the function is run it caches the plan for one of the values and will never use the appropriate plan for the second value.
No, it plans based on a sort of "generic value", not the first one you
supply. The way to get at that plan is:
PREPARE foo AS <query>;
EXPLAIN EXECUTE foo (parameters);
...Robert
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-04-12 13:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-10 20:47 function performs differently with different values Ben Chobot <bench@silentmedia.com>
2010-04-12 13:21 ` Robert Haas <robertmhaas@gmail.com>
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