Re: Optimze usage of immutable functions as relation

From: Aleksandr Parfenov <a(dot)parfenov(at)postgrespro(dot)ru>
To: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Optimze usage of immutable functions as relation
Date: 2018-05-16 10:47:46
Message-ID: 20180516134746.0296a05f@asp437-g082ur
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hello,

I reworked a patch to make more stable in different cases. I decided to
use simplify_function instead of eval_const_expression to prevent
inlining of the function. The only possible outputs of the
simplify_function are Const node and NULL.

Also, I block pre-evaluation of functions with types other than
TYPTYPE_BASE, cause there is no special logic for compound (and others)
values yet.

There is still a problem with memory leak in case of simplified
arguments. The only way I see is a creation of temporary memory context,
but it cost some performance. Maybe we can store simplified arguments
in the pointed function itself for later use. But eval_const_expression
and friends doesn't change the content of the nodes inside the tree, it
generates new nodes and returns it as a result.

The last point to mention is a fixed plan for the query in the initial
letter of the thread. As I mentioned before, new versions of the patch
replace var not with a function call, but with a function execution
result. After the patch, the following plan is used instead of Nested
Loop with Sequence Scan:

explain select '|'||subject||'|', ts_rank_cd(body_tsvector,q) from messages, to_tsquery('english', 'tuple&header&overhead') q where body_tsvector @@ q limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Limit (cost=224.16..266.11 rows=3 width=36)
-> Nested Loop (cost=224.16..266.11 rows=3 width=36)
-> Function Scan on q (cost=0.00..0.01 rows=1 width=0)
-> Bitmap Heap Scan on messages (cost=224.16..266.04 rows=3 width=275)
Recheck Cond: (body_tsvector @@ '''tupl'' & ''header'' & ''overhead'''::tsquery)
-> Bitmap Index Scan on message_body_idx (cost=0.00..224.16 rows=3 width=0)
Index Cond: (body_tsvector @@ '''tupl'' & ''header'' & ''overhead'''::tsquery)

--
Aleksandr Parfenov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Attachment Content-Type Size
funcscan_plan_optimizer_v2.patch text/x-patch 5.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2018-05-16 10:50:04 Re: Postgres 11 release notes
Previous Message Alexander Korotkov 2018-05-16 10:43:16 Re: [BUGFIX] amcanbackward is not checked before building backward index paths