Optimze usage of immutable functions as relation - Mailing list pgsql-hackers

Hi hackers,

There is a strange behavior of the query planner in some cases if
stable/immutable was used a relation. In some cases, it affects costs of
operations and leads to a bad plan of the execution. Oleg Bartunov 
noticed
such behavior in queries with a to_tsvector as a relation:

=# explain select '|'||subject||'|', ts_rank_cd(body_tsvector,q) from 
messages, to_tsquery('tuple&header&overhead') q where body_tsvector @@ 
q;
                                         QUERY PLAN
------------------------------------------------------------------------------------------
  Nested Loop  (cost=383.37..58547.70 rows=4937 width=36)
    ->  Function Scan on to_tsquery q  (cost=0.25..0.26 rows=1 width=32)
    ->  Bitmap Heap Scan on messages  (cost=383.12..58461.04 rows=4937 
width=275)
          Recheck Cond: (body_tsvector @@ q.q)
          ->  Bitmap Index Scan on message_body_idx  (cost=0.00..381.89 
rows=4937 width=0)
                Index Cond: (body_tsvector @@ q.q)
(6 rows)

=# explain select '|'||subject||'|', ts_rank_cd(body_tsvector,q) from 
messages, to_tsquery('tuple&header&overhead') q where body_tsvector @@
q limit 10;
                                    QUERY PLAN
--------------------------------------------------------------------------------
  Limit  (cost=0.25..425.62 rows=10 width=36)
    ->  Nested Loop  (cost=0.25..210005.80 rows=4937 width=36)
          Join Filter: (messages.body_tsvector @@ q.q)
          ->  Function Scan on to_tsquery q  (cost=0.25..0.26 rows=1 
width=32)
          ->  Seq Scan on messages  (cost=0.00..197625.45 rows=987445 
width=275)

The idea of the fix for this situation is to check is a result of the
function constant or not during the planning of the query. Attached 
patch does
this by processing Var entries at planner stage and replace them with
constant value if it is possible. Plans after applying a patch (SeqScan
query for comparison):

=# explain select '|'||subject||'|', ts_rank_cd(body_tsvector,q) from 
messages, to_tsquery('tuple&header&overhead') q where body_tsvector @@
q limit 10;
                                           QUERY PLAN
----------------------------------------------------------------------------------------------
  Limit  (cost=224.66..268.11 rows=3 width=36)
    ->  Nested Loop  (cost=224.66..268.11 rows=3 width=36)
          ->  Function Scan on to_tsquery q  (cost=0.25..0.26 rows=1 
width=0)
          ->  Bitmap Heap Scan on messages  (cost=224.41..267.04 rows=3 
width=275)
                Recheck Cond: (body_tsvector @@ 
to_tsquery('tuple&header&overhead'::text))
                ->  Bitmap Index Scan on message_body_idx  
(cost=0.00..224.41 rows=3 width=0)
                      Index Cond: (body_tsvector @@ 
to_tsquery('tuple&header&overhead'::text))
(7 rows)

=# set enable_bitmapscan=off;
SET
=# explain select '|'||subject||'|', ts_rank_cd(body_tsvector,q) from 
messages, to_tsquery('tuple&header&overhead') q where body_tsvector @@
q limit 10;
                                         QUERY PLAN
------------------------------------------------------------------------------------------
  Limit  (cost=1000.25..296754.14 rows=3 width=36)
    ->  Gather  (cost=1000.25..296754.14 rows=3 width=36)
          Workers Planned: 2
          ->  Nested Loop  (cost=0.25..295753.32 rows=1 width=36)
                ->  Parallel Seq Scan on messages
(cost=0.00..295752.80 rows=1 width=275)
                      Filter: (body_tsvector @@ 
to_tsquery('tuple&header&overhead'::text))
                ->  Function Scan on to_tsquery q  (cost=0.25..0.26 
rows=1 width=0)
(7 rows)

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