Wyszukiwarka szybsza o 60-80%: optymalizacja CTE w PostgreSQL pod Node.js

Problem: wyszukiwarka robiła się coraz wolniejsza

Nasz endpoint wyszukiwarki w e-commerce działał za wolno. W miarę jak rósł katalog produktów, zapytania, które kiedyś śmigały w 200ms, zaczęły podchodzić pod 800ms. Dla wyszukiwarki to nie do przyjęcia — użytkownicy oczekują natychmiastowych wyników.

Endpoint korzysta z CTE (Common Table Expressions) w PostgreSQL, żeby zbudować złożony wynik wyszukiwania obejmujący produkty, powiązane aktualności, kolekcje i kategorie. Po sprofilowaniu znalazłem trzy wzorce, które zabijały wydajność.

Antywzorzec #1: podwójny JOIN na tej samej tabeli

Problem

ProductMetadata AS (
    SELECT DISTINCT
        fp_limited.id AS product_id,
        product."collectionId" AS collection_id,
        product."mainCategoryId" AS category_id
    FROM (
        SELECT fp.id
        FROM FinalProductsPool fp
        JOIN public."Product" AS p ON p."id" = fp.id
        ORDER BY ...
        LIMIT 100
    ) AS fp_limited
    JOIN public."Product" AS product ON product."id" = fp_limited.id
)

Widzisz problem? Joinujemy tabelę Product dwa razy:

Raz wewnątrz podzapytania, żeby posortować
Raz na zewnątrz, żeby wyciągnąć collectionId i mainCategoryId

Każdy join to pełne wyszukanie. Przy tabeli liczącej ponad 50 000 produktów to kosztowna zabawa.

Rozwiązanie

Przenieś wybór pól do podzapytania:

ProductMetadata AS (
    SELECT
        fp_limited.id AS product_id,
        fp_limited.collection_id,
        fp_limited.category_id
    FROM (
        SELECT
            fp.id,
            p."collectionId" AS collection_id,
            p."mainCategoryId" AS category_id
        FROM FinalProductsPool fp
        JOIN public."Product" AS p ON p."id" = fp.id
        ORDER BY ...
        LIMIT 100
    ) AS fp_limited
)

Jeden join zamiast dwóch. Te same dane, o połowę mniej roboty.

Antywzorzec #2: podzapytania z NOT IN

Problem

NewsFromCollections AS (
    SELECT DISTINCT n."id", 2 AS priority
    FROM ProductMetadata pm
    JOIN public."_newsCollections" nc ON nc."A" = pm.collection_id
    JOIN public."News" n ON n."id" = nc."B"
    WHERE
        pm.collection_id IS NOT NULL
        AND n."type" = 'Article'
        AND n."id" NOT IN (SELECT id FROM NewsFromProducts)  -- SLOW
),
NewsFromCategories AS (
    SELECT DISTINCT n."id", 3 AS priority
    FROM ProductMetadata pm
    JOIN public."_newsCategories" ncat ON ncat."A" = pm.category_id
    JOIN public."News" n ON n."id" = ncat."B"
    WHERE
        pm.category_id IS NOT NULL
        AND n."type" = 'Article'
        AND n."id" NOT IN (SELECT id FROM NewsFromProducts)    -- SLOW
        AND n."id" NOT IN (SELECT id FROM NewsFromCollections) -- SLOW
)

Podzapytania z NOT IN to wydajnościowy koszmar PostgreSQL. Dla każdego wiersza baza musi:

Wykonać podzapytanie
Sprawdzić, czy bieżące ID występuje w zbiorze wyników
Obsłużyć przypadki brzegowe z NULL-ami (które całkowicie zmieniają zachowanie)

Przy zagnieżdżonych CTE to się kumuluje — PostgreSQL nie zawsze potrafi optymalizować w poprzek granic CTE.

Rozwiązanie

Zamień NOT IN na LEFT JOIN + IS NULL:

NewsFromCollections AS (
    SELECT DISTINCT n."id", 2 AS priority
    FROM ProductMetadata pm
    JOIN public."_newsCollections" nc ON nc."A" = pm.collection_id
    JOIN public."News" n ON n."id" = nc."B"
    LEFT JOIN NewsFromProducts nfp ON nfp."id" = n."id"
    WHERE
        pm.collection_id IS NOT NULL
        AND n."type" = 'Article'
        AND nfp."id" IS NULL  -- Exclude already-found news
),
NewsFromCategories AS (
    SELECT DISTINCT n."id", 3 AS priority
    FROM ProductMetadata pm
    JOIN public."_newsCategories" ncat ON ncat."A" = pm.category_id
    JOIN public."News" n ON n."id" = ncat."B"
    LEFT JOIN NewsFromProducts nfp ON nfp."id" = n."id"
    LEFT JOIN NewsFromCollections nfc ON nfc."id" = n."id"
    WHERE
        pm.category_id IS NOT NULL
        AND n."type" = 'Article'
        AND nfp."id" IS NULL
        AND nfc."id" IS NULL
)

Dlaczego to jest szybsze?

LEFT JOIN może korzystać z indeksów i wchodzi w skład planu zapytania. Baza z góry wie, co robi, i potrafi zoptymalizować całą sekwencję joinów. NOT IN wymusza sprawdzanie wiersz po wierszu, którego nie da się zaplanować równie efektywnie.

Antywzorzec #3: zbędne CTE z osobnym sortowaniem

Problem

UniqueCollectionsRaw AS (
    SELECT DISTINCT ON (col."id")
        col."id",
        col."name",
        asset."fullpath" AS "mainPhotoFullPath",
        col."priority"
    FROM ProductMetadata pm
    JOIN public."Collection" col ON col."id" = pm.collection_id
    LEFT JOIN public."Asset" asset ON col."mainPhotoId" = asset."id"
    WHERE col."isPublished" = true
    ORDER BY col."id"
),
UniqueCollections AS (
    SELECT id, name, "mainPhotoFullPath"
    FROM UniqueCollectionsRaw
    ORDER BY priority DESC
    LIMIT 5
)

Dwa CTE tam, gdzie wystarczyłoby jedno. Pierwsze deduplikuje po id, drugie sortuje na nowo po priority. PostgreSQL potrafi zrobić jedno i drugie w jednym przebiegu.

Rozwiązanie

Połącz to w jedno CTE z sensowną agregacją:

UniqueCollections AS (
    SELECT
        col."id",
        col."name",
        asset."fullpath" AS "mainPhotoFullPath"
    FROM (
        SELECT DISTINCT pm.collection_id
        FROM ProductMetadata pm
        WHERE pm.collection_id IS NOT NULL
    ) AS distinct_collections
    JOIN public."Collection" col ON col."id" = distinct_collections.collection_id
    LEFT JOIN public."Asset" asset ON col."mainPhotoId" = asset."id"
    WHERE col."isPublished" = true
    ORDER BY col."priority" DESC
    LIMIT 5
)

Jedno CTE zamiast dwóch. Najpierw deduplikacja (mniejszy zbiór danych), dopiero potem join i sortowanie.

Łączny efekt

Po wdrożeniu wszystkich trzech optymalizacji:

Metryka	Przed	Po	Poprawa
Średni czas zapytania	780ms	180ms	77% szybciej
Czas zapytania P95	1.2s	320ms	73% szybciej
CPU bazy na zapytanie	Wysokie	Niskie	~60% mniej

Przy szerokich wyszukiwaniach (pojedyncza litera albo popularne słowo) poprawa była jeszcze bardziej wyraźna — sięgała blisko 80%.

Jak znaleźć te wzorce u siebie w kodzie

1. Szukaj podwójnych joinów

Wyłap miejsca, w których joinujesz tabelę w podzapytaniu, a potem joinujesz ją ponownie na zewnątrz:

FROM (
    SELECT ... FROM table1 JOIN table2 ...
) AS subquery
JOIN table2 ...  -- Same table again!

2. Znajdź podzapytania z NOT IN

grep -r "NOT IN (SELECT" src/

Prawie każde NOT IN (SELECT ...) da się zastąpić przez LEFT JOIN ... IS NULL.

3. Sprawdź łańcuchy CTE

Jeśli masz CTE, które tylko przemielają poprzednie CTE bez żadnych nowych joinów:

CTE_A AS (SELECT ... ORDER BY x),
CTE_B AS (SELECT * FROM CTE_A ORDER BY y LIMIT n)

Rozważ ich połączenie.

Wskazówki do optymalizacji CTE w PostgreSQL

1. CTE to bariery optymalizacyjne (zazwyczaj)

PostgreSQL tradycyjnie traktuje CTE jak bariery optymalizacyjne — nie wstawia ich inline ani nie przepycha do nich predykatów. Zmieniło się to w PostgreSQL 12 wraz z NOT MATERIALIZED, ale wiele wzorców wciąż zyskuje na ręcznej optymalizacji.

2. Agreguj przed joinowaniem

Jeśli joinujesz dużą tabelę tylko po to, żeby dostać kilka unikalnych wartości, najpierw wyciągnij te wartości:

-- Slow: Join first, deduplicate later
SELECT DISTINCT col.* FROM big_table bt JOIN collection col ON ...

-- Fast: Deduplicate first, join smaller set
SELECT col.* FROM (
    SELECT DISTINCT collection_id FROM big_table
) AS ids
JOIN collection col ON col.id = ids.collection_id

3. LEFT JOIN IS NULL kontra NOT EXISTS

Oba są lepsze od NOT IN, ale przy sprawdzaniu istnienia NOT EXISTS bywa czasem szybszy:

-- Good
LEFT JOIN other_table ot ON ot.id = main.id
WHERE ot.id IS NULL

-- Sometimes better (especially with proper indexes)
WHERE NOT EXISTS (SELECT 1 FROM other_table ot WHERE ot.id = main.id)

Sprofiluj oba warianty w swoim konkretnym przypadku.

Mierzenie wydajności zapytań

EXPLAIN ANALYZE

Przy optymalizacji zawsze sięgaj po EXPLAIN ANALYZE:

EXPLAIN ANALYZE
WITH ...your CTEs...
SELECT ...

Szukaj:

Seq Scan na dużych tabelach (brakujący indeks?)
Nested Loop przy dużej liczbie wierszy (zła kolejność joinów?)
operacji Sort bez indeksu (dołożyć indeks czy przepisać zapytanie?)

W Prismie

Dla surowych zapytań w Prismie:

const results = await prisma.$queryRaw`
  EXPLAIN ANALYZE
  ${yourQuery}
`
console.log(results)

Wnioski

Trzy proste wzorce sprawiały, że nasza wyszukiwarka była 4x wolniejsza, niż musiała być:

Podwójne JOIN-y: joinuj raz i od razu wybierz wszystko, czego potrzebujesz
Podzapytania z NOT IN: użyj zamiast tego LEFT JOIN IS NULL
Zbędne CTE: łącz operacje tam, gdzie się da

Poprawka to 23 zmienione linijki w zapytaniu liczącym ponad 1000 linii. Żadnych zmian w schemacie, żadnych nowych indeksów, żadnych podbić infrastruktury — po prostu mądrzejszy SQL.

Jeśli twoje zapytania w PostgreSQL są wolne, zacznij od poszukania tych wzorców. Rozwiązanie może być prostsze, niż myślisz.

Prawdziwa optymalizacja endpointu wyszukiwarki e-commerce obsługującego 11 rynków europejskich. Czas zapytania spadł z 780ms do 180ms.