Re: Optimze usage of immutable functions as relation

From: Aleksandr Parfenov <a(dot)parfenov(at)postgrespro(dot)ru>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, 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-09-06 11:51:09
Message-ID: 20180906185109.661aaef0@asp437-ThinkPad-L380
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tue, 10 Jul 2018 17:34:20 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>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; QUERY PLAN
>> -------------------------------------------------------------------
>> 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
> regards, tom lane

I reworked the patch and implemented processing of FuncScan in
pull_up_subqueries() in a way similar to VALUES processing. In order to
prevent folding of non-foldable functions it checks provolatile of the
function and are arguments const or not and return type to prevent
folding of SRF.

Aleksandr Parfenov
Postgres Professional:
Russian Postgres Company

Attachment Content-Type Size
funcscan_plan_optimizer_v3.patch text/x-patch 6.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2018-09-06 12:04:39 Re: remove ancient pre-dlopen dynloader code
Previous Message Chris Travers 2018-09-06 11:44:00 Re: Funny hang on PostgreSQL 10 during parallel index scan on slave