🥇PostgreSQL Antipatterns: CTE x CTE

By type of activity, one has to deal with situations when a developer writes a request and thinks “the base is smart, it can handle everything by itself!«

In some cases (partly from ignorance of the database capabilities, partly from premature optimizations), this approach leads to the appearance of "Frankensteins".

First, I will give an example of such a request:

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

In order to objectively evaluate the quality of a request, let's create some arbitrary data set:

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

It turns out that just reading data took less than a quarter of the time query execution:

[look at explain.tensor.ru]

We disassemble the bones

Let's take a closer look at the request, and wonder:

Why is WITH RECURSIVE here, if there are no recursive CTEs?
Why group min/max values in a separate CTE if they are then tied to the original selection anyway?
+25% time
Why use a reread from the previous CTE at the end with an unconditional 'SELECT * FROM'?
+14% time

In this case, we were still very lucky that Hash Join was chosen for the connection, and not Nested Loop, because then we would get not a single CTE Scan, but 10K!

a little about CTE ScanHere we must remember that CTE Scan is an analogue of Seq Scan - that is, no indexing, but only a complete search, which would require 10K x 0.3ms = 3000ms for cycles on cte_max or 1K x 1.5ms = 1500ms when looping over cte_bind!
Actually, what did you want to get as a result? Yeah, usually just such a question is visited somewhere in the 5th minute of parsing “three-story” requests.

We wanted for each unique key pair to output min/max from group by key_a.
So let's use this window functions:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

[look at explain.tensor.ru]

Since reading data in both cases takes approximately 4-5ms equally, then all our gain in time -32% - it's pure load removed from base CPU, if such a query is executed frequently enough.

In general, it is not necessary to force the base "round - to wear, square - to roll."

Source: habr.com

PostgreSQL Antipatterns: CTE x CTE

We disassemble the bones

Add a comment Отменить ответ