[PERFORM] optimizing immutable vs. stable function calls?

От: Karl Czajkowski
Тема: [PERFORM] optimizing immutable vs. stable function calls?
Дата: ,
Msg-id: 20170118223633.GC23081@moraine.isi.edu
(см: обсуждение, исходный текст)
Список: pgsql-performance

I originally was thinking I had a performance problem related to
row-level security but in reducing it to a simple test case I realized
it applies more generally.  The query planner does not seem to
recognize that it can eliminate redundant calls to a STABLE function.
It will optimize the same call if the function is marked IMMUTABLE.

In my case, the function call does not take any arguments and is thus
trivially independent of row data, and appears in a WHERE clause being
compared to constants. Why wouldn't the optimizer treat this case the
same as IMMUTABLE?

Toy problem

This abstracts a scenario where a web application stores client
identity attributes (i.e. roles or groups) into a config variable
which can be accessed from a stored procedure to allow policy checks
against that client context.

The example query emulates a policy that is a disjunction of two

  1. clients who intersect a constant ACL
     '{C,E,Q}'::text[] && current_attributes()

  2. rows whose "cls" intersect a constant mask
     cls = ANY('{0,1,5}'::text[])

This is a common idiom for us, where some rows are restricted from the
general user base but certain higher privilege clients can see all

So our test query is simply:

  FROM mydata
  WHERE '{C,E,Q}'::text[] && current_attributes()
     OR cls = ANY('{0,1,5}'::text[])

Test setup

I set a very high cost on the function to attempt to encourage the
planner to optimize away the calls, but it doesn't seem to make any
difference.  Based on some other discussions I have seen, I also tried
declaring it as LEAKPROOF but saw no change in behavior there either.

  CREATE OR REPLACE FUNCTION current_attributes() RETURNS text[]
  STABLE COST 1000000
  AS $$
    RETURN current_setting('mytest.attributes')::text[];
  $$ LANGUAGE plpgsql;

  CREATE TABLE mydata (
    id serial PRIMARY KEY,
    val integer,
    cls text

  INSERT INTO mydata (val, cls)
  SELECT v, (v % 13)::text
  FROM generate_series(1, 1000000, 1) AS s (v);

  CREATE INDEX ON mydata(cls);
  ANALYZE mydata;

Resulting plans and performance

These results are with PostgreSQL 9.5 on a Fedora 25 workstation but I
see essentially the same behavior on 9.6 as well.

For an intersecting ACL scenario, I set client context as:

  SELECT set_config('mytest.attributes', '{A,B,C,D}', False);

and for non-intersecting, I set:

  SELECT set_config('mytest.attributes', '{}', False);

In an ideal world, the planner knows it can solve the ACL intersection
once, independent of any row data and then form a different plan
optimized around that answer, the same as if we'd just put a constant
true or false term in our WHERE clause.

A. STABLE function for intersecting ACL

   Seq Scan on mydata  (cost=0.00..2500021656.00 rows=238030 width=10) (actual time=0.053..1463.382 rows=1000000
     Filter: (('{C,E,Q}'::text[] && current_attributes()) OR (cls = ANY ('{0,1,5}'::text[])))
   Planning time: 0.093 ms
   Execution time: 1500.395 ms

B. IMMUTABLE function for intersecting ACL

   Seq Scan on mydata  (cost=0.00..15406.00 rows=1000000 width=10) (actual time=0.009..78.474 rows=1000000 loops=1)
   Planning time: 0.247 ms
   Execution time: 117.610 ms

C. STABLE function for non-intersecting ACL

   Seq Scan on mydata  (cost=0.00..2500021656.00 rows=238030 width=10) (actual time=0.179..1190.484 rows=230770
     Filter: (('{C,E,Q}'::text[] && current_attributes()) OR (cls = ANY ('{0,1,5}'::text[])))
     Rows Removed by Filter: 769230
   Planning time: 0.088 ms
   Execution time: 1199.729 ms

D. IMMUTABLE function for non-intersecting ACL

   Bitmap Heap Scan on mydata  (cost=4058.36..12631.44 rows=230333 width=10) (actual time=32.444..76.934 rows=230770
     Recheck Cond: (cls = ANY ('{0,1,5}'::text[]))
     Heap Blocks: exact=5406
     ->  Bitmap Index Scan on mydata_cls_idx  (cost=0.00..4000.78 rows=230333 width=0) (actual time=31.012..31.012
           Index Cond: (cls = ANY ('{0,1,5}'::text[]))
   Planning time: 0.331 ms
   Execution time: 87.475 ms

You can see the roughly 10-15x performance difference above. In our
real application with more sprawling data, sorting, and lots of
available column indices, the effects are even more pronounced.

Is there any hope for the planner to start optimizing these
row-independent, stable function calls the same way it does immutable

We tend to start a transaction, set the config parameter with the web
client identity attributes, and then run the other
performance-sensitive statements to completion (or error) in the same
transaction.  We don't further modify the parameter during the
lifetime of one web request handler.  We cycle through different
parameter settings only when we reuse a connection for multiple web
requests which may all be from different clients.

Is it safe to lie and call the function IMMUTABLE to get good plans,
even though we do modify the config value infrequently during one
postgres connection?  Does postgres ever cache immutable function
results between statements?  If so, is there any way to invalidate
that cache without fully closing and reopening a connection?



В списке pgsql-performance по дате сообщения:

От: Karl Czajkowski
Сообщение: Re: [PERFORM] optimizing immutable vs. stable function calls?
От: Clailson
Сообщение: [PERFORM] Optimization inner join