On 4/21/05, Enrico Weigelt <[EMAIL PROTECTED]> wrote:
>
> > Even if your data never changes it *can* change so the function should
> > be at most stable not immutable.
>
> okay, the planner sees that the table could potentionally change.
> but - as the dba - I'd like to tell him, this table *neve
On 4/21/05, Enrico Weigelt <[EMAIL PROTECTED]> wrote:
> * Tom Lane <[EMAIL PROTECTED]> wrote:
>
>
> > Yeah, I was actually thinking about a two-step process: inline the
> > function to produce somethig equivalent to a handwritten scalar
> > sub-SELECT, and then try to convert sub-SELECTs into joi
* Tom Lane <[EMAIL PROTECTED]> wrote:
> Yeah, I was actually thinking about a two-step process: inline the
> function to produce somethig equivalent to a handwritten scalar
> sub-SELECT, and then try to convert sub-SELECTs into joins.
... back to my original question ...
What kind of query sho
* Jaime Casanova <[EMAIL PROTECTED]> wrote:
> Even if your data never changes it *can* change so the function should
> be at most stable not immutable.
okay, the planner sees that the table could potentionally change.
but - as the dba - I'd like to tell him, this table *never* changes
in practi
Yay! You're right! I wonder why have I forgotten about LANGUAGE SQL. :)
It's 30 seconds vs 5 seconds for CASE ... END insisde PLpgsql vs CASE...END
LANGUAGE SQL. :) I.e. its almost the same as in-place entered SQL.
Regards,
Dawid
---(end of broadcast)-
Yay! You're right! I wonder why have I forgotten about LANGUAGE SQL. :)
It's 30 seconds vs 5 seconds for CASE ... END insisde PLpgsql vs CASE...END
LANGUAGE SQL. :) I.e. its almost the same as in-place entered SQL.
Probably because simple SQL functions get inlined by the optimiser.
Chris
---
On 4/20/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> You should re-run the function test using SQL as the function language
> instead of plpgsql. There might be some performance to be had there.
Yay! You're right! I wonder why have I forgotten about LANGUAGE SQL. :)
It's 30 seconds vs 5 seconds
You should re-run the function test using SQL as the function language
instead of plpgsql. There might be some performance to be had there.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do
Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> It would be interesting sometime to try to teach the planner about
>> inlining SQL-language functions to become joins. That is, given
> The Inlining of the function is presumably a side-issue. I have tons of
> querie
Tom Lane <[EMAIL PROTECTED]> writes:
> It would be interesting sometime to try to teach the planner about
> inlining SQL-language functions to become joins. That is, given
>
> create function id2name(int) returns text as
> 'select name from mytab where id = $1' language sql stable;
>
> select u
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
>> d) self-join with a function ;)
>> EXPLAIN ANALYZE SELECT * FROM (SELECT n, id2username(n) AS username
>> FROM (SELECT DISTINCT n FROM aaa) AS values) AS v_lookup RIGHT JOIN
>> aaa USING (n);
> That's pretty clever.
> It sure seems like the server
On 4/18/05, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> > d) self-join with a function ;)
> > EXPLAIN ANALYZE SELECT * FROM (SELECT n, id2username(n) AS username
> > FROM (SELECT DISTINCT n FROM aaa) AS values) AS v_lookup RIGHT JOIN
> > aaa USING (n);
>
> That's pretty clever.
> It sure seems l
> d) self-join with a function ;)
> EXPLAIN ANALYZE SELECT * FROM (SELECT n, id2username(n) AS username
> FROM (SELECT DISTINCT n FROM aaa) AS values) AS v_lookup RIGHT JOIN
> aaa USING (n);
That's pretty clever.
It sure seems like the server was not caching the results of the
function...maybe
On 4/15/05, Enrico Weigelt <[EMAIL PROTECTED]> wrote:
> a) SELECT items.a, items.b, ..., users.username FROM items, users
> WHERE items.uid = users.uid;
>
> c) CREATE FUNCTION id2username(oid) RETURNS text
> LANGUAGE 'SQL' IMMUTABLE AS '
> SELECT username AS RESULT FROM users W
On 4/17/05, Enrico Weigelt <[EMAIL PROTECTED]> wrote:
> * Tom Lane <[EMAIL PROTECTED]> wrote:
> > Enrico Weigelt <[EMAIL PROTECTED]> writes:
> > > c) CREATE FUNCTION id2username(oid) RETURNS text
> > > LANGUAGE 'SQL' IMMUTABLE AS '
> > > SELECT username AS RESULT FROM users WHERE uid = $1';
* Tom Lane <[EMAIL PROTECTED]> wrote:
> Enrico Weigelt <[EMAIL PROTECTED]> 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* immutabl
Enrico Weigelt <[EMAIL PROTECTED]> 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 ... yo
Hi folks,
I like to use (immutable) functions for looking up serveral
(almost constant) things, i.e fetching a username by id.
This makes my queries more clear.
But is this really performant ?
Lets imagine:
We've got an table with user accounts (uid,name,...). Then we've
got another one whi
18 matches
Mail list logo
