What performs higher, SQL FILTER or CASE?

on

|

views

and

comments

[ad_1]

I’ve discovered an attention-grabbing query on Twitter, lately. Is there any efficiency impression of utilizing FILTER in SQL (PostgreSQL, particularly), or is it simply syntax sugar for a CASE expression in an combination operate?

As a fast reminder, FILTER is an superior customary SQL extension to filter out values earlier than aggregating them in SQL. That is very helpful when aggregating a number of issues in a single question.

These two are the identical:

SELECT 
  fa.actor_id,

  -- These:
  SUM(size) FILTER (WHERE ranking = 'R'),
  SUM(size) FILTER (WHERE ranking = 'PG'),

  -- Are the identical as these:
  SUM(CASE WHEN ranking = 'R' THEN size END),
  SUM(CASE WHEN ranking = 'PG' THEN size END)
FROM film_actor AS fa 
LEFT JOIN movie AS f 
  ON f.film_id = fa.film_id
GROUP BY fa.actor_id

As of jOOQ 3.17, these SQL dialects are identified to assist FILTER natively:

  • CockroachDB
  • Firebird
  • H2
  • HSQLDB
  • PostgreSQL
  • SQLite
  • YugabyteDB

Ought to it matter?

However again to the query. Does it actually matter when it comes to efficiency? Ought to it? Clearly, it shouldn’t matter. The 2 forms of combination operate expressions will be confirmed to imply precisely the identical factor. And actually, that’s what jOOQ does when you’re utilizing FILTER on some other SQL dialect. Put the above question in our SQL translation software, translate to Oracle, for instance, and also you’ll be getting:

SELECT
  fa.actor_id,
  sum(CASE WHEN ranking = 'R' THEN size END),
  sum(CASE WHEN ranking = 'PG' THEN size END),
  sum(CASE WHEN ranking = 'R' THEN size END),
  sum(CASE WHEN ranking = 'PG' THEN size END)
FROM film_actor fa
  LEFT JOIN movie f
    ON f.film_id = fa.film_id
GROUP BY fa.actor_id

The opposite means must be potential as properly in an optimiser.

Does it matter?

However is that this being achieved? Let’s strive evaluating the next 2 queries on PostgreSQL, towards the sakila database:

Question 1:

SELECT 
  fa.actor_id,
  SUM(size) FILTER (WHERE ranking = 'R'),
  SUM(size) FILTER (WHERE ranking = 'PG')
FROM film_actor AS fa 
LEFT JOIN movie AS f 
  ON f.film_id = fa.film_id
GROUP BY fa.actor_id

Question 2:

SELECT 
  fa.actor_id,
  SUM(CASE WHEN ranking = 'R' THEN size END),
  SUM(CASE WHEN ranking = 'PG' THEN size END)
FROM film_actor AS fa 
LEFT JOIN movie AS f 
  ON f.film_id = fa.film_id
GROUP BY fa.actor_id

I shall be utilizing this benchmark approach, and can submit the benchmark code on the finish of this weblog submit. The outcomes of working every question 500x are clear (much less time is best):

Run 1, Assertion 1: 00:00:00.786621
Run 1, Assertion 2: 00:00:00.839966

Run 2, Assertion 1: 00:00:00.775477
Run 2, Assertion 2: 00:00:00.829746

Run 3, Assertion 1: 00:00:00.774942
Run 3, Assertion 2: 00:00:00.834745

Run 4, Assertion 1: 00:00:00.776973
Run 4, Assertion 2: 00:00:00.836655

Run 5, Assertion 1: 00:00:00.775871
Run 5, Assertion 2: 00:00:00.845209

There’s a constant 8% efficiency penalty for utilizing the CASE syntax, in comparison with the FILTER syntax on my machine, working PostgreSQL 15 in docker. The precise distinction in a non-benchmark question is probably not as spectacular, or extra spectacular, relying on {hardware} and information units. However clearly, one factor appears to be a bit higher on this case than the opposite.

Since these kind of syntaxes are sometimes utilized in a reporting context, the variations can positively matter.

Including an auxiliary predicate

You would possibly suppose there’s extra optimisation potential, if we make the predicates on the RATING column redundant, like this:

Question 1:

SELECT 
  fa.actor_id,
  SUM(size) FILTER (WHERE ranking = 'R'),
  SUM(size) FILTER (WHERE ranking = 'PG')
FROM film_actor AS fa 
LEFT JOIN movie AS f 
  ON f.film_id = fa.film_id
  AND ranking IN ('R', 'PG') -- Redundant predicate right here
GROUP BY fa.actor_id

Question 2:

SELECT 
  fa.actor_id,
  SUM(CASE WHEN ranking = 'R' THEN size END),
  SUM(CASE WHEN ranking = 'PG' THEN size END)
FROM film_actor AS fa 
LEFT JOIN movie AS f 
  ON f.film_id = fa.film_id
  AND ranking IN ('R', 'PG')
GROUP BY fa.actor_id

Be aware it needs to be positioned within the LEFT JOIN‘s ON clause, so as to not tamper with the outcomes. It may’t be positioned within the question’s WHERE clause. A proof for this distinction is right here.

What is going to the benchmark yield now?

Run 1, Assertion 1: 00:00:00.701943
Run 1, Assertion 2: 00:00:00.747103

Run 2, Assertion 1: 00:00:00.69377
Run 2, Assertion 2: 00:00:00.746252

Run 3, Assertion 1: 00:00:00.684777
Run 3, Assertion 2: 00:00:00.745419

Run 4, Assertion 1: 00:00:00.688584
Run 4, Assertion 2: 00:00:00.740979

Run 5, Assertion 1: 00:00:00.688878
Run 5, Assertion 2: 00:00:00.742864

So, certainly, the redundant predicate improved issues (in an ideal world, it shouldn’t, however right here we’re. The optimiser doesn’t optimise this in addition to it may). However nonetheless, the FILTER clause outperforms CASE clause utilization.

Conclusion

In an ideal world, two provably equal SQL syntaxes additionally carry out the identical means. However this isn’t at all times the case in the true world, the place optimisers make tradeoffs between:

  • Time spent optimising uncommon syntaxes
  • Time spent executing queries

In a earlier weblog submit (which might be outdated by now), I’ve proven a variety of these instances, the place the optimisation resolution doesn’t depend upon any value mannequin and information units and may at all times be achieved, ideally. There was an inclination of such optimisations being favoured by RDBMS which have an execution plan cache (e.g. Db2, Oracle, SQL Server), in case of which the optimisation must be achieved solely as soon as per cached plan, after which the plan will be reused. In RDBMS that don’t have such a cache, optimisation time is extra pricey per question, so much less will be anticipated.

I feel this can be a case the place it’s value trying into easy patterns of expressions in combination features. AGG(CASE ..) is such a preferred idiom, and eight% is kind of the numerous enchancment, that I feel PostgreSQL ought to repair this. We’ll see. In any case, since FILTER is already:

  • Higher performing
  • Higher trying

You may safely swap to this good customary SQL syntax already now.

Benchmarking

Whereas on this case, the advance is value it regardless of precise measurements (as a result of efficiency can hardly be worse, and readability really improves), at all times watch out with such benchmark outcomes. Do measure issues your self, and when you can not reproduce a efficiency downside, then don’t essentially contact completely sound logic simply because a weblog submit advised you so.

Benchmark code

As promised, this was the benchmark code used for this weblog submit:

DO $$
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT INT := 500;
  rec RECORD;
BEGIN

  -- Repeat the entire benchmark a number of occasions to keep away from warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := clock_timestamp();

    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT 
          fa.actor_id,
          SUM(size) FILTER (WHERE ranking = 'R'),
          SUM(size) FILTER (WHERE ranking = 'PG')
        FROM film_actor AS fa 
        LEFT JOIN movie AS f 
          ON f.film_id = fa.film_id
          AND ranking IN ('R', 'PG')
        GROUP BY fa.actor_id
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    RAISE INFO 'Run %, Assertion 1: %', r, (clock_timestamp() - v_ts);
    v_ts := clock_timestamp();

    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT 
          fa.actor_id,
          SUM(CASE WHEN ranking = 'R' THEN size END),
          SUM(CASE WHEN ranking = 'PG' THEN size END)
        FROM film_actor AS fa 
        LEFT JOIN movie AS f 
          ON f.film_id = fa.film_id
          AND ranking IN ('R', 'PG')
        GROUP BY fa.actor_id
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    RAISE INFO 'Run %, Assertion 2: %', r, (clock_timestamp() - v_ts);
    RAISE INFO '';
  END LOOP;
END$$;

The benchmark approach is described right here.



[ad_2]

Supply hyperlink

Share this
Tags

Must-read

Google Presents 3 Suggestions For Checking Technical web optimization Points

Google printed a video providing three ideas for utilizing search console to establish technical points that may be inflicting indexing or rating issues. Three...

A easy snapshot reveals how computational pictures can shock and alarm us

Whereas Tessa Coates was making an attempt on wedding ceremony clothes final month, she posted a seemingly easy snapshot of herself on Instagram...

Recent articles

More like this

LEAVE A REPLY

Please enter your comment!
Please enter your name here