From: Tom Lane <tgl@sss.pgh.pa.us>
To: weigelt@metux.de
Cc: pgsql-performance@postgresql.org
Subject: Re: immutable functions vs. join for lookups ?
Date: Fri, 15 Apr 2005 17:12:47 -0400
Message-ID: <6776.1113599567@sss.pgh.pa.us> (raw)
In-Reply-To: <20050415205511.GB20345@nibiru.borg.metux.de>
References: <20050415205511.GB20345@nibiru.borg.metux.de>
Enrico Weigelt <weigelt@metux.de> writes:
> c) CREATE FUNCTION id2username(oid) RETURNS text
> LANGUAGE 'SQL' IMMUTABLE AS '
> SELECT username AS RESULT FROM users WHERE uid = $1';
This is simply dangerous. The function is *NOT* immutable (it is
stable though). When ... not if ... your application breaks because
you got the wrong answers, you'll get no sympathy from anyone.
The correct question to ask was "if I make a stable function like
this, is it likely to be faster than the join?". The answer is
"probably not; at best it will be equal to the join". The best the
planner is likely to be able to do with the function-based query
is equivalent to a nestloop with inner indexscan (assuming there is
an index on users.uid). If that's the best plan then the join case
should find it too ... but if you are selecting a lot of items rows
then it won't be the best plan.
regards, tom lane
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, weigelt@metux.de
Subject: Re: immutable functions vs. join for lookups ?
In-Reply-To: <6776.1113599567@sss.pgh.pa.us>
* 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