Performance difference between postgREST filtering and native WHERE clause

[BorislavZlatanov]

I have a plpgsql function serving an endpoint under /rpc/poll. The function returns a query which joins three small tables - one with 700 rows, the second 3000 and the third with 8000 rows.

When I use postgREST filtering such as http://localhost:3001/rpc/poll?author=eq.author_name&permlink=eq.mypermlink, it takes 5.46 seconds for the page with the result to load. When instead I put the author and permlink as parameters in the function and add them to the WHERE clause, the page with the result loads in 223 ms. In the latter case the request becomes http://localhost:3001/rpc/poll?v_author=author_Name&v_permlink=mypermlink. Both examples are on localhost as you can see.

Is this difference in performance expected? I am not sure of exactly how the postgREST filtering works under the hood.

My tables look like this:

CREATE TABLE IF NOT EXISTS polls (
  id SERIAL PRIMARY KEY,
  author VARCHAR(255) NOT NULL,
  permlink TEXT NOT NULL,
  [...]
);
CREATE TABLE IF NOT EXISTS poll_choices (
  id SERIAL PRIMARY KEY,
  poll_id INT NOT NULL,
  choice_num INT NOT NULL,
  choice_text TEXT NOT NULL,
  FOREIGN KEY(poll_id) 
    REFERENCES polls(id),
  UNIQUE (poll_id, choice_num)
);
CREATE TABLE IF NOT EXISTS poll_votes (
  id SERIAL PRIMARY KEY,
  account VARCHAR(255) NOT NULL,
  poll_id INT NOT NULL,
  choices INT[],
  voted_at TIMESTAMP NOT NULL,
  [...]
);

And the function (very bulky, the variables are in the WHERE at the end):

CREATE FUNCTION poll(v_author VARCHAR = NULL, v_permlink VARCHAR = NULL)
RETURNS TABLE (post_title TEXT, post_body TEXT, author VARCHAR, created TIMESTAMP, permlink TEXT, parent_permlink TEXT, parent_author TEXT, tags JSONB, image JSONB, protocol_version NUMERIC, question TEXT, preferred_interpretation VARCHAR, token VARCHAR, end_time TIMESTAMP, status TEXT, max_choices_voted INT, filter_account_age_days INT, ui_hide_res_until_voted BOOLEAN, platform VARCHAR, poll_trx_id VARCHAR, poll_choices JSON, poll_voters JSONB, poll_stats JSON)
AS $$

BEGIN
RETURN QUERY

WITH votes_filtered AS (
SELECT DISTINCT ON(pv.id) pv.*
FROM polls p
JOIN poll_choices pc
ON p.id = pc.poll_id
JOIN poll_votes pv
ON pc.poll_id = pv.poll_id
AND pc.choice_num = ANY(pv.choices)
JOIN account_data ad 
ON pv.account = ad.account
WHERE pv.choices IS NOT NULL
-- Poll end time
AND (CASE 
  WHEN p.end_time IS NOT NULL THEN
  pv.voted_at <= p.end_time
  ELSE TRUE
END)
-- Account age filter
AND (CASE 
  WHEN p.filter_account_age_days IS NOT NULL THEN
  (pv.voted_at::date - ad.acc_created::date) >= p.filter_account_age_days
  ELSE TRUE
END)
)
SELECT 
  p.post_title,
  p.post_body, 
  p.author, 
  p.created, 
  p.permlink,
  p.parent_permlink,
  p.parent_author,
  p.tags,
  p.image,
  p.protocol_version,
  p.question,
  p.preferred_interpretation,
  p.token,
  p.end_time,
  CASE 
    WHEN p.end_time IS NULL THEN 'Active'
    WHEN NOW() < p.end_time THEN 'Active'
    ELSE 'Ended'
  END AS status,
  p.max_choices_voted,
  p.filter_account_age_days,
  p.ui_hide_res_until_voted,
  p.platform,
  p.trx_id,
    -- Nest under poll_choices field
    (SELECT json_agg(t1)
    FROM (
    SELECT choice_num,
    choice_text,
      -- Nest under votes field
      (SELECT json_build_object(
      'total_votes', t2.total_votes,
      'hive_hp', t2.hp,
      'hive_proxied_hp', t2.proxied_hp,
      'hive_hp_incl_proxied', t2.hp_incl_proxied,
      'spl_spsp', t2.spsp,
      CASE WHEN p.token IS NOT NULL AND SUBSTRING(LOWER(p.token), 1, 3) = 'he:' THEN 'he_' || SUBSTRING(LOWER(p.token), 4) ELSE 'he_token' END, t2.he_token,
      'colony_colonyp', t2.colonyp,
      'glx_glxp', t2.glxp
      ) 
      FROM (
      SELECT COUNT(*) AS total_votes, TRUNC(SUM(hp / 1000.0), 3) AS hp, TRUNC(SUM(proxied_hp / 1000.0), 3) AS proxied_hp, TRUNC(SUM((hp + proxied_hp) / 1000.0), 3) AS hp_incl_proxied, TRUNC(SUM(spsp / 1000.0), 3) AS spsp, TRIM_SCALE(SUM(he_token / 10000000000.0)) AS he_token, TRUNC(SUM(colonyp / 1000.0), 3) AS colonyp, TRUNC(SUM(glxp / 1000.0), 3) AS glxp
      FROM votes_filtered vf
      WHERE pc.poll_id = vf.poll_id
      AND pc.choice_num = ANY(vf.choices)
      GROUP BY vf.poll_id) t2
      ) AS votes
    FROM poll_choices pc
    WHERE pc.poll_id = p.id) t1
    ) AS poll_choices,
    -- Nest under poll_voters field
    (SELECT jsonb_agg(jsonb_build_object(
      'name', t1.account,
      'choices', t1.choices,
      'hive_hp', t1.hp,
      'hive_proxied_hp', t1.proxied_hp,
      'hive_hp_incl_proxied', t1.hp_incl_proxied,
      'spl_spsp', t1.spsp,
      CASE WHEN p.token IS NOT NULL AND SUBSTRING(LOWER(p.token), 1, 3) = 'he:' THEN 'he_' || SUBSTRING(LOWER(p.token), 4) ELSE 'he_token' END, t1.he_token,
      'colony_colonyp', t1.colonyp,
      'glx_glxp', t1.glxp
      ))
      FROM (
      SELECT account, choices, TRUNC(hp / 1000.0, 3) AS hp, TRUNC(proxied_hp / 1000.0, 3) AS proxied_hp, TRUNC((hp + proxied_hp) / 1000.0, 3) AS hp_incl_proxied, TRUNC(spsp / 1000.0, 3) AS spsp, TRIM_SCALE(he_token / 10000000000.0) AS he_token, TRUNC(colonyp / 1000.0, 3) AS colonyp, TRUNC(glxp / 1000.0, 3) AS glxp
      FROM votes_filtered vf
      WHERE vf.poll_id = p.id) t1
    ) AS poll_voters,
    -- Nest under poll_stats field
    (SELECT json_build_object(
      'total_voting_accounts_num', t1.total_voting_accounts_num,
      'total_hive_hp', t1.hp,
      'total_hive_proxied_hp', t1.proxied_hp,
      'total_hive_hp_incl_proxied', t1.hp_incl_proxied,
      'total_spl_spsp', t1.spsp,
      CASE WHEN p.token IS NOT NULL AND SUBSTRING(LOWER(p.token), 1, 3) = 'he:' THEN 'total_he_' || SUBSTRING(LOWER(p.token), 4) ELSE 'total_he_token' END, t1.he_token,
      'total_colony_colonyp', t1.colonyp,
      'total_glx_glxp', t1.glxp
      )
      FROM (
        SELECT COUNT(account) AS total_voting_accounts_num, TRUNC(SUM(hp / 1000.0), 3) AS hp, TRUNC(SUM(proxied_hp / 1000.0), 3) AS proxied_hp, TRUNC(SUM((hp + proxied_hp) / 1000.0), 3) AS hp_incl_proxied, TRUNC(SUM(spsp / 1000.0), 3) AS spsp, TRIM_SCALE(SUM(he_token / 10000000000.0)) AS he_token, TRUNC(SUM(colonyp / 1000.0), 3) AS colonyp, TRUNC(SUM(glxp / 1000.0), 3) AS glxp
        FROM votes_filtered vf
        WHERE p.id = vf.poll_id
        GROUP BY vf.poll_id
      ) t1
    ) AS poll_stats
FROM polls p
WHERE 
-- Poll author
CASE 
  WHEN v_author IS NOT NULL THEN p.author = v_author ELSE TRUE
END
-- Poll permlink
AND CASE 
  WHEN v_permlink IS NOT NULL THEN p.permlink = v_permlink ELSE TRUE
END;
      
END;
$$
LANGUAGE plpgsql; 

[wolfgangwalther]

Is this difference in performance expected?

Yes, because the arguments are applied on the query inside the function, while the non-argument filters are applied on the result outside of the function. Since your function is a plpgsql, volatile function it is not inline-able and PostgreSQL can't push your conditions into the inner query. Thus the query inside the function runs to completion and is filtered afterwards.

It seems your function can be marked STABLE. Also replace it with a pure SQL function instead of plpgsql. This should give you a chance of inlining the function call - you can then see whether the =eq. style request performs better. If it doesn't, make sure to look at the explain output of the query to check whether inlining actually happened or not.

[BorislavZlatanov]

Thank you very much for the explanation and the pointers. I did as you suggested and thankfully it does inline the function. Performance actually went down to 82 ms. Thanks again.

[steve-chavez]

I wonder if we could somehow warn about these cases. We know that a function cannot be inlined and that there are filters applied to it at runtime.

Perhaps we emit a WARNING log?

Another idea that occurs to me is a metric for non-inlinable function usage, but maybe it doesn't make much sense since it wouldn't bring that much attention.

[wolfgangwalther]

I wonder if we could somehow warn about these cases. We know that a function cannot be inlined and that there are filters applied to it at runtime.

I think we should start with something like a "how to make PostgREST fast" section in the docs, How-To style or such. Just to document all the good things that we can do?

I mentioned this discussion in #3246.