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.