Re: Optimze usage of immutable functions as relation

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Cc: Aleksandr Parfenov <a(dot)parfenov(at)postgrespro(dot)ru>, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Optimze usage of immutable functions as relation
Date: 2018-07-10 21:34:20
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> But stepping back a bit, it's a bit weird that we're handling this
> differently from VALUES and other subqueries. The planner knows how to
> do this trick for simple subqueries:

> postgres=# explain select * from tenk1, (select abs(100)) as a (a) where
> unique1 < a;
> -----------------------------------------------------------
> Seq Scan on tenk1 (cost=0.00..483.00 rows=100 width=248)
> Filter: (unique1 < 100)
> (2 rows)

> Note that it not only evaluated the function into a constant, but also
> got rid of the join. For a function RTE, however, it can't do that:

> postgres=# explain select * from tenk1, abs(100) as a (a) where unique1 < a;
> -------------------------------------------------------------------
> Nested Loop (cost=0.00..583.01 rows=3333 width=248)
> Join Filter: (tenk1.unique1 < a.a)
> -> Function Scan on a (cost=0.00..0.01 rows=1 width=4)
> -> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
> (4 rows)

> Could we handle this in pull_up_subqueries(), similar to the

Perhaps. You could only do it for non-set-returning functions, which
isn't the typical use of function RTEs, which is probably why we've not
thought hard about it before. I'm not sure what would need to happen for
lateral functions. Also to be considered, if it's not foldable to a
constant, is whether we're risking evaluating it more times than before.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2018-07-10 21:36:17 Re: Desirability of client-side expressions in psql?
Previous Message Tom Lane 2018-07-10 21:29:50 Re: Jsonb transform for pl/python