Dlaczego CTE jest znacznie gorszy niż wbudowane podzapytania

Próbuję lepiej zrozumieć, jak działa narzędzie do planowania zapytań w postgresql.

Mam to zapytanie:

select id from users 
    where id <> 2
    and gender = (select gender from users where id = 2)
    order by latest_location::geometry <-> (select latest_location from users where id = 2) ASC
    limit 50

Działa w mojej bazie danych w czasie krótszym niż 10 ms z około 500 000 wpisów w tabeli użytkowników.

Potem pomyślałem, że aby uniknąć duplikatów podselekcji, mógłbym przepisać zapytanie jako CTE, tak jak to:

with me as (
    select * from users where id = 2
)
select u.id, u.popularity from users u, me 
    where u.gender = me.gender
    order by  u.latest_location::geometry <-> me.latest_location::geometry ASC
    limit 50;

Jednak to przepisane zapytanie działa w ciągu około 1 sekundy! Dlaczego to się dzieje? Widzę w wyjaśnieniach, że nie używa indeksu geometrii, ale czy można coś z tym zrobić? Dzięki!

Innym sposobem napisania zapytania jest:

select u.id, u.popularity from users u, (select gender, latest_location from users where id = 2) as me 
    where u.gender = me.gender
    order by  u.latest_location::geometry <-> me.latest_location::geometry ASC
    limit 50;

Będzie to jednak równie powolne jak CTE.

Jeśli z drugiej strony wyodrębnię parametry me i wstawię je statycznie, zapytanie ponownie jest szybkie:

select u.id, u.popularity from users u
    where u.gender = 'male'
    order by  u.latest_location::geometry <-> '0101000000A49DE61DA71C5A403D0AD7A370F54340'::geometry ASC
    limit 50;

Wyjaśnij pierwsze (szybkie) zapytanie

 Limit  (cost=5.69..20.11 rows=50 width=36) (actual time=0.512..8.114 rows=50 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using users_pkey on users users_1  (cost=0.42..2.64 rows=1 width=32) (actual time=0.032..0.033 rows=1 loops=1)
           Index Cond: (id = 2)
   InitPlan 2 (returns $1)
     ->  Index Scan using users_pkey on users users_2  (cost=0.42..2.64 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Index Scan using users_latest_location_gix on users  (cost=0.41..70796.51 rows=245470 width=36) (actual time=0.509..8.100 rows=50 loops=1)
         Order By: (latest_location <-> $0)
         Filter: (gender = $1)
         Rows Removed by Filter: 20
 Total runtime: 8.211 ms
(12 rows)

Wyjaśnij drugie (wolne) zapytanie

Limit  (cost=62419.82..62419.95 rows=50 width=76) (actual time=1024.963..1024.970 rows=50 loops=1)
   CTE me
     ->  Index Scan using users_pkey on users  (cost=0.42..2.64 rows=1 width=221) (actual time=0.037..0.038 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Sort  (cost=62417.18..63030.86 rows=245470 width=76) (actual time=1024.959..1024.963 rows=50 loops=1)
         Sort Key: ((u.latest_location <-> me.latest_location))
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Hash Join  (cost=0.03..54262.85 rows=245470 width=76) (actual time=0.122..938.131 rows=288646 loops=1)
               Hash Cond: (u.gender = me.gender)
               ->  Seq Scan on users u  (cost=0.00..49353.41 rows=490941 width=48) (actual time=0.021..465.025 rows=490994 loops=1)
               ->  Hash  (cost=0.02..0.02 rows=1 width=36) (actual time=0.054..0.054 rows=1 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                     ->  CTE Scan on me  (cost=0.00..0.02 rows=1 width=36) (actual time=0.047..0.049 rows=1 loops=1)
 Total runtime: 1025.096 ms

Spróbuj tego:

with me as (
    select * from users where id = 2
)
select u.id, u.popularity from users u, me 
    where u.gender = (select gender from me)
    order by  u.latest_location::geometry <-> (select latest_location from me)::geometry ASC
    limit 50;

Kiedy patrzę na szybki plan, oto, co na mnie wyskakuje (pogrubione):

 Limit (koszt = 5,69..20,11 wierszy = 50 szerokość = 36) (rzeczywisty czas = 0,512..8,114 wierszy = 50 pętli = 1)
   InitPlan 1 ( zwraca 0 USD )
     -> Skanowanie indeksu za pomocą klucza users_pkey dla użytkowników users_1 (koszt = 0,42..2,64 wiersza = 1 szerokość = 32) (rzeczywisty czas = 0,032..0,033 wierszy = 1 pętla = 1)
           Indeks Cond: (id = 2)
   InitPlan 2 ( zwraca 1 $ )
     -> Skanowanie indeksu za pomocą klucza users_pkey na użytkownikach users_2 (koszt = 0,42..2,64 wiersza = 1 szerokość = 4) (rzeczywisty czas = 0,009..0,010 wierszy = 1 pętla = 1)
           Indeks Cond: (id = 2)
   -> Skanowanie indeksu przy użyciu users_latest_location_gix na użytkownikach (koszt = 0,41..70796,51 wierszy = 245470 szerokość = 36) (czas rzeczywisty = 0,509..8,100 wierszy = 50 pętli = 1)
         Sortuj według: (najnowsza lokalizacja   0 USD )
         Filtr: (płeć = 1 USD )
         Rzędy usunięte przez filtr: 20
 Całkowity czas działania: 8,211 ms
(12 rzędów)

W wersji wolnej narzędzie do planowania zapytań ocenia operator równości włączony genderi operator geometrii włączony latest_locationw kontekście łączenia , w którym wartość memoże się różnić dla każdego wiersza (mimo że poprawnie oszacował tylko 1 wiersz). W szybkiej wersji wartości genderi latest_locationsą traktowane jako skalary, ponieważ są emitowane przez wbudowane podzapytania, co oznacza, że ​​planista zapytań ma do dyspozycji tylko jedną wartość. Jest to ten sam powód, dla którego dostajesz szybki plan, gdy wklejasz wartości dosłowne.