Common Table Expressions - WITH Clause

Giriş

Açıklaması şöyle. Yani bir select sonucu gelen tablo üzerinde daha fazla select yapılabilir. Böylece iç içe sorgulardan kaçabiliriz. Yani subquery'nin üste yazılmış hali gibi düşünülebilir.

Common table expressions (CTEs) are also known as 'WITH' queries. They're a nice way to avoid deeply nested subqueries.

Söz dizimi şöyle. CTE aynı zamanda WITH clause olarak ta bilinir

WITH cte_name AS (cte_body)

"Common Table Expressions" genellikle "Analytic Functions / Window Functions" ile birlikte kullanılırlar

1. Common Table Expressions Neden Lazım

Çünkü nested veya subquery sorgular çok fazla iç içe geçebiliyor. CTE ile bu yapı düzleştiriliyor ve okuması kolaylaşıyor

Örnek

CTE ile şöyle yaparız. Burada 2 tane CTE tablosu oluşturuluyor. İsimleri engineers ve eu_engineers

WITH engineers AS(
 SELECT * FROM employees WHERE
  dept="Engineering"
),
eu_engineers AS (
  SELECT * FROM engineers 
  WHERE country IN ("NL",...)
)
SELECT * FROM eu_engineers WHERE ...

Subquery  ile şöyle yaparız

SELECT * FROM (
 SELECT * FROM (SELECT * employees WHERE
  dept="Engineering") AS engineers
  WHERE country IN ("NL",...))
WHERE ...

2. CTE İsmi Zaten Varsa - Existing Table Name

Soru MySQL ile ilgili ancak cevap aslında aynı. Eğer elimizde mevcut bir tablo varsa ve CTE içinde de bu tablo ismini kullanırsak ne olur? Kural şöyle

derived tables > CTEs (table defined in a WITH block) > everything else

3. Kullanım

Örnek

Şöyle yaparız

WITH my_expression AS (
  SELECT customer AS name FROM my_table
)
SELECT name FROM my_expression

Örnek

Şöyle yaparız. Burada CTE tablo ismi T, daha sonra ilk ve son satırına erişiliyor.

WITH T AS (
   SELECT id, coins_id, first_coin, second_coin, price, `time`
   FROM hist_all
   WHERE `time` BETWEEN (NOW() - interval 120 minute) AND NOW()
     AND (second_coin = 'USD' OR second_coin = 'USDT')
     AND first_coin = 'LSK'
) 
(SELECT * FROM T ORDER BY time LIMIT 1)
UNION ALL
(SELECT * FROM T ORDER BY time DESC LIMIT 1);

Örnek

Şöyle yaparız

WITH idtempp as (
  SELECT id as id
  FROM id 
  WHERE country = "US"
  AND status = "Y"
)

SELECT *
FROM bill
WHERE id in (SELECT id from idtempp)

Örnek

Tablonun ilk hali şöyle. Yani subquery kullanıyor.

SELECT
    users.id,
    users.name,
    COUNT(DISTINCT orders.id) AS order_count,
    SUM(orders.amount) AS total_spent,
    MAX(logins.timestamp) AS last_login
FROM users
LEFT JOIN orders ON users.id = orders.user_id
LEFT JOIN logins ON users.id = logins.user_id
WHERE users.created_at >= '2023-01-01'
GROUP BY users.id;

CTE kullanarak şöyle yaparız

WITH recent_users AS (
    SELECT id, name
    FROM users
    WHERE created_at >= '2023-01-01'
),
order_stats AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
    FROM orders
    GROUP BY user_id
),
last_logins AS (
    SELECT user_id, MAX(timestamp) AS last_login
    FROM logins
    GROUP BY user_id
)
SELECT
    u.id,
    u.name,
    o.order_count,
    o.total_spent,
    l.last_login
FROM recent_users u
LEFT JOIN order_stats o ON u.id = o.user_id
LEFT JOIN last_logins l ON u.id = l.user_id;

4. Dikkat Edilmesi Gereken Hususlar

CTE ile geçici bir tablo yaratılır. Bu tablo bir kere yaratılır ve tekrar tekrar kullanılır.  Açıklaması şöyle

A useful property of WITH queries is that they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects

Ancak bazen ana tablodaki index'ler CTE tablosuna aktarılamıyor. Açıklaması şöyle

However, the other side of this coin is that the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary subquery.

Örnek

Şöyle yaparız. Arada çok fazla süre farkı var. Aslında bu yeni PostgreSQL ile düzeltilmiş ama sadece örnek olsun diye aldım.

> CREATE TABLE foo (id INT, padding TEXT);
> INSERT INTO foo (id, padding) SELECT id, md5(random()::text) FROM
  generate_series(1, 1000000) AS id ORDER BY random();
> CREATE INDEX foo_id_ix ON foo (id);

> SELECT * FROM foo WHERE id = 500000;
...
Time: 0.619 ms

> WITH CTE AS (SELECT * FROM foo) SELECT * FROM cte WHERE id = 500000;
...
Time: 227.675 ms

Sebebini görmek için şöyle yaparız Index Scan yerine CTE Scan yapılıyor

EXPLAIN (ANALYZE ON, TIMING ON) SELECT * FROM foo WHERE id = 500000;
QUERY PLAN
— — — — — — — — — — — — — — — 
Index Scan using foo_id_ix on foo (cost=0.42..8.44 rows=1 width=37) (actual time=0.026..0.028 rows=1 loops=1)
    Index Cond: (id = 500000)
Execution time: 0.060 ms

EXPLAIN (ANALYZE ON, TIMING ON) WITH CTE AS (SELECT * FROM foo) 
SELECT * FROM CTE WHERE id = 500000;
QUERY PLAN
------------------------------
CTE Scan on cte  (cost=18334.00..40834.00 rows=5000 width=36) (actual time=3.243..269.290 rows=1 loops=1)
  Filter: (id = 500000)
  Rows Removed by Filter: 999999
  CTE cte
    ->  Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.029..77.078 rows=1000000 loops=1)
Execution time: 276.625 ms

Eğer subquery kullanırsak çıktı şöyle. Yine Index Scan kullanılıyor

EEXPLAIN (ANALYZE ON, TIMING ON) SELECT * FROM (SELECT * FROM foo) AS subquery WHERE id = 500000;
QUERY PLAN
------------------------------
Index Scan using foo_id_ix on foo  (cost=0.42..8.44 rows=1 width=37) (actual time=0.028..0.031 rows=1 loops=1)
  Index Cond: (id = 500000)
Execution time: 0.066 ms

WITH RECURSIVE - Graph Sorgular İçindir

Örnek

Açıklaması şöyle

Postgres' SELECT DISTINCT doesn't scale. Here's why and how to fix it.  👇 

Let's say you need to query distinct values from a large table, for example, a "device_id" column with a few possible values but many rows associated to each device.

The table has a B-tree index on the device_id column, i.e., the index entries are sorted by device_id.

SELECT DISTINCT should cost only as many lookups as there are devices. That's what I expected from a sorted index: find the first device, jump past it, find the next one, and so on.

Postgres has no plan node for "jump past it". The planner picks an Index Only Scan, reads all the index entries in order, then pick the one you're looking for with a "Unique" node that throws away all the rows you don't care for, potentially millions of them.

The work is proportional to the number of rows, not to the number of distinct values.

MySQL knows how to handle this: it calls this operation a "loose index scan". (PG 18 ships a new "skip scan" that does a similar thing for non-leading column.)

The fix is to build the loose scan out of two pieces Postgres does have: seeks and recursive CTEs.

Burada problem kullanılan B-tree yapısının sadece iki şeyi iyi yapabilmesi.

1. İleri yürüme

2. İstenilen değeri bulma - seek

Eğer tablolar çok büyükse index scan yapsa bile uzun sürüyor. Çünkü tüm indeksi dolaşıyor ve daha sonra distinct işlemine sokuyor.  Yani Postgres şunu yapamıyor. 

I'm on A. Jump directly to the first value greater than A

Bu durumda 1 seek per distinct value haline getirmek lazım. 

Elimizde şöyle bir sorgu olsun

WITH RECURSIVE devices AS (
    -- Anchor
    (
        SELECT device_id
        FROM tasks
        ORDER BY device_id
        LIMIT 1
    )

    UNION ALL

    -- Recursive step
    SELECT (
        SELECT device_id
        FROM tasks
        WHERE device_id > devices.device_id
        ORDER BY device_id
        LIMIT 1
    )
    FROM devices
    WHERE devices.device_id IS NOT NULL
)
SELECT device_id
FROM devices
WHERE device_id IS NOT NULL;

Bu sorgu B-tree seek haline getirir.