Function inlining performance on many-to-many relationships

[zachblume]

With function inlinig, we can define the computed relationship for many-to-many.

However, compared to an explicit join definition, function inlining sometimes creates a significantly worse plan:

analyze;

CREATE OR REPLACE FUNCTION alltime(people)
  RETURNS SETOF alltime_slim
  AS $$
  SELECT
    alltime_slim.*
  FROM
    alltime_slim
  WHERE alltime_slim.name_concat = $1.name_concat
$$
LANGUAGE SQL
STABLE;
ANALYZE;

--benchmark alltime slim with nameconcat join against postgrest  --- 32 seconds
explain select
      count(*)
    from
      "public"."people"
    where
      (
        (
          exists (
            select
              1
            from
              "public"."alltime" ("people"::"public"."people") as "alltime_1"
            where
              (("alltime_1"."transaction_amt" >= 1))
          )
        )
      )
      and organization_id='org_2uzvaHmlBp70TMWJ8THdRkwRQB3'
      
      ;;
--      Aggregate  (cost=3702083.04..3702083.05 rows=1 width=8)
--  ->  Index Scan using people_organization_id_idx on people  (cost=0.56..3695460.10 rows=2649179 width=0)
--        Index Cond: (organization_id = 'org_2uzvaHmlBp70TMWJ8THdRkwRQB3'::text)
--        Filter: (SubPlan 1)
--        SubPlan 1
--          ->  Index Only Scan using idx_alltime_slim_name_concat_transaction_amt on alltime_slim  (cost=0.57..80.89 rows=3741 width=0)
--                Index Cond: ((name_concat = (people.*).name_concat) AND (transaction_amt >= 1))


-- handwritten query 3 seconds -- 10x faster!
explain select
      count(*)
    from
      "public"."people"
    where
      (
        (
          exists (
            select
              1
            from
              alltime_slim
            where
              (("alltime_slim"."transaction_amt" >= 1)) and alltime_slim.name_concat=people.name_concat
          )
        )
      )
      and organization_id='org_2uzvaHmlBp70TMWJ8THdRkwRQB3'
      
      ;;
--Finalize Aggregate  (cost=1957948.64..1957948.65 rows=1 width=8)
--  ->  Gather  (cost=1957948.53..1957948.64 rows=1 width=8)
--        Workers Planned: 1
--        ->  Partial Aggregate  (cost=1956948.53..1956948.54 rows=1 width=8)
--              ->  Nested Loop Semi Join  (cost=1.12..1956904.32 rows=17681 width=0)
--                    ->  Parallel Index Only Scan using idx_people_org_name on people  (cost=0.56..125120.75 rows=3116681 width=20)
--                          Index Cond: (organization_id = 'org_2uzvaHmlBp70TMWJ8THdRkwRQB3'::text)
--                    ->  Index Only Scan using idx_alltime_slim_name_concat_transaction_amt on alltime_slim  (cost=0.57..75.42 rows=3741 width=20)
--                          Index Cond: ((name_concat = people.name_concat) AND (transaction_amt >= 1))

[zachblume]

Interestingly the same with a scalar input:

CREATE OR REPLACE FUNCTION alltime(p_name text)
  RETURNS SETOF alltime_slim
  LANGUAGE sql
  IMMUTABLE
AS $$
  SELECT s.*
  FROM alltime_slim s
  WHERE s.name_concat = p_name
$$;


explain SELECT count(*)
FROM people
WHERE EXISTS (
  SELECT 1
  FROM alltime(people.name_concat) AS at
  WHERE at.transaction_amt >= 1
)
  AND organization_id = 'org_2uzvaHmlBp70TMWJ8THdRkwRQB3';
--  Aggregate  (cost=3269920.95..3269920.96 rows=1 width=8)
--  ->  Index Only Scan using idx_people_org_name on people  (cost=0.56..3263306.05 rows=2645962 width=0)
--        Index Cond: (organization_id = 'org_2uzvaHmlBp70TMWJ8THdRkwRQB3'::text)
--        Filter: (SubPlan 1)
--        SubPlan 1
--          ->  Index Only Scan using idx_alltime_slim_name_concat_transaction_amt on alltime_slim s  (cost=0.57..82.25 rows=3809 width=0)
--                Index Cond: ((name_concat = people.name_concat) AND (transaction_amt >= 1))

[wolfgangwalther]

The fast plan seems to make use of parallel query execution (although there is only 1 worker... odd).

You might need to set PARALLEL SAFE on your function, otherwise this plan would be rejected?

[zachblume]

Ah well, this is on a toy db versus the production db with 64 core.

In any case, I actually physically inlined the function myself and got the same:

explain select
      count(*)
    from
      "public"."people"
    where
      (
        (
          exists (
            select
              1
            from
              (
                SELECT
    alltime_slim.*
  FROM
    alltime_slim
  WHERE alltime_slim.name_concat = people.name_concat
  ) as "alltime_1"
            where
              (("alltime_1"."transaction_amt" >= 1))
          )
        )
      )
      and organization_id='org_2uzvaHmlBp70TMWJ8THdRkwRQB3'
      
      ;;
--      Aggregate  (cost=3267810.33..3267810.34 rows=1 width=8)
--  ->  Index Only Scan using idx_people_org_name on people  (cost=0.56..3261199.99 rows=2644133 width=0)
--        Index Cond: (organization_id = 'org_2uzvaHmlBp70TMWJ8THdRkwRQB3'::text)
--        Filter: (SubPlan 1)
--        SubPlan 1
--          ->  Index Only Scan using idx_alltime_slim_name_concat_transaction_amt on alltime_slim  (cost=0.57..82.03 rows=3798 width=0)
--                Index Cond: ((name_concat = people.name_concat) AND (transaction_amt >= 1))

So perhaps this is just a limitation of function inlining versus composite indexes

[wolfgangwalther]

Hm. This is probably more a question for the PostgreSQL mailing lists than here - aka, why PG can't make the same optimization in this case?

[zachblume]

I'll bring it there.

Can you imagine any break-glass in the future that postgREST could extend functionality to allow the definition of indexes to join by, that are not FKs or inlinable functions that have this limitation, for relations?

[wolfgangwalther]

Well our answer to the question of "can you imagine [...] to allow [...] to join by [...] not FKs?"... was the invention of those computed relationships in form of inline-able functions. So.. we still don't have any other, better, ideas.