potrzebna pomoc przy indeksach w postgresie

[januzi 24]

czy ktoś zna na tyle postgresa, że mógłby podpowiedzieć jaki indeks dołożyć do zapytania:

 

SELECT
           way, surface,
           COALESCE(CASE WHEN landuse = 'forest' THEN 'wood' ELSE NULL END, "natural") AS "natural",
           CASE WHEN "natural" = 'mud'
               THEN "natural"
               ELSE CASE WHEN ("natural" = 'wetland' AND NOT tags ? 'wetland')
                 THEN 'wetland'
                 ELSE CASE WHEN ("natural" = 'wetland')
                   THEN tags->'wetland'
                   ELSE NULL
                   END
               END
             END AS int_wetland,
           tags->'leaf_type' AS leaf_type,
           way_area/NULLIF(POW(545979*0.001*0.28,2),0) AS way_pixels
         FROM planet_osm_polygon
         WHERE ("natural" IN ('mud', 'wetland', 'wood', 'beach', 'shoal', 'reef', 'scrub', 'sand') OR landuse = 'forest')
           AND building IS NULL
           AND way_area > 1*152.874::real*152.874::real
         ORDER BY COALESCE(layer,0), way_area DESC

żeby wykonywało się ono szybciej i żeby explain analyze nie zwracało wyników typu:

Sort  (cost=813535.97..813553.26 rows=6915 width=318) (actual time=208780.325..211611.095 rows=302446 loops=1)
  Sort Key: (COALESCE(layer, 0)), way_area DESC
  Sort Method: external merge  Disk: 221952kB
  ->  Seq Scan on planet_osm_polygon  (cost=0.00..813094.95 rows=6915 width=318) (actual time=65.277..197295.161 rows=302446 loops=1)
        Filter: ((building IS NULL) AND (way_area > '23370.4577112803'::double precision) AND (("natural" = ANY ('{mud,wetland,wood,beach,shoal,reef,scrub,sand}'::text[])) OR (landuse = 'forest'::text)))
        Rows Removed by Filter: 13136953
Planning time: 590.256 ms
Execution time: 211884.360 ms
(8 rows)

[Karister 1533]

Wg mnie to zapytanie jest problemem samo w sobie. To:

 

COALESCE(CASE WHEN landuse = 'forest' THEN 'wood' ELSE NULL END, "natural") AS "natural"

buduje kolumnę natural, która może mieć dwie wartości: 'wood', gdy kolumna landuse ma wartość 'forest', albo 'natural' w przeciwnym razie. Po co więc ten IN w WHERE? Samo landuse = 'forest' da to samo, bo wiadomo, że IN(..., 'wood', ...) będzie spełniony. Wtedy landuse jest dobrym kandydatem na index.

Podobna uwaga do CASE WHEN "natural" = 'mud'. Po co, skoro to nigdy się nie spełni?

Nie kumam też rzutowania i mnożenia w WHERE. Nie można od razu przyrównać do żądanej wartości?

 

Pozostałe kolumny w tabeli to loteria w indeksowaniu, zależnie od danych w tabeli. IS NULL na building ma szanse skorzystać z indexu, jeśli tych NULLi jest odsetek wszystkich wartości. Podobnie z warunkiem większości na way_area. Jeśli odfiltrowuje on jakieś 95% wszystkich wartości, to index ma sens. W przeciwnym razie, jego użycie może być kosztowniejsze od full scana.

[januzi 24]

Takie coś jest w domyślnych stylach dla open street mapa. To zewnętrze way_area jest chyba liczone na żywca, dlatego jest wstawiane w taki sposób:

AND way_area > 0.01*!pixel_width!::real*!pixel_height!::real

 

W innych miejscach jest dużo gorzej, np. zapytanie ma pięć ekranów (widok konsolowy, po połączeniu przez ssh), w którym jest 5 selectów w unionie, z where typu:

FROM planet_osm_line
             WHERE (tunnel IS NULL OR NOT tunnel IN ('yes', 'building_passage'))
               AND (covered IS NULL OR NOT covered = 'yes')
               AND (bridge IS NULL OR NOT bridge IN ('yes', 'boardwalk', 'cantilever', 'covered', 'low_water_crossing', 'movable', 'trestle', 'viaduct'))
               AND highway IS NOT NULL -- end of road select

 

Tutaj za cholerę nie da się chyba wrzucić indeksów (dałem na highway, ale nie pomogło).

 

Może pomóc trochę systemowi i dorzucić własne kolumny, które będą zawierać tylko to co potrzeba? Jak w tym where powyżej? Coś na zasadzie: moja_kolumna (boolean, tinyint, o ile takie są w postgresie?) i potem update set moja_kolumna = 1 where ( ). Na koniec indeks na to i w samym zapytaniu zmienić where na moja_kolumna = 1.

[Karister 1533]

Nie ma definicji aliasu way_area w zapytaniu, więc IMO jest to kolumna.

 

Nie kumam, jak chcesz dodać kolumny do istniejących tabel. Przecież tam są już jakieś dane i jeśli coś je zmieni, to Twoje nowe kolumny się same nie zaktualizują. Jak chcesz iść w tym kierunku, to próbowałbym stworzyć widok z tymi danymi, co potrzebujesz, a oryginalne tabele zaindeksował. Widok będzie SELECTował oryginalne tabele i wykorzystywał ich indexy (wg uznania query plannera). Ale to nie będzie szybsze od SELECTa korzystającego z tych indexów. Więc wracasz do punktu wyjścia - grzebanie w tych SQLach. Chyba, że będzie to widok zmaterializowany. Je chyba można indexować, ale nie wiem, jak one działają.

Edytowane 19 Sierpnia 2019 przez Karister

[januzi 24]

Raczej aktualizacji nie będzie. To co siedzi będzie tylko czytane. Więc dodatkowe kolumny w niczym nie będą przeszkadzać, a mogą tylko pomóc w wyciąganiu danych.

 

 

I jeszcze jedno:

SELECT DISTINCT ON (p.way)	
           p.way AS way, l.highway AS int_tc_type,
           CASE WHEN l.service IN ('parking_aisle', 'drive-through', 'driveway')
             THEN 'INT-minor'::text
             ELSE 'INT-normal'::text
           END AS int_tc_service
         FROM planet_osm_point p
           JOIN planet_osm_line l
             ON ST_DWithin(p.way, l.way, 0.1) -- Assumes Mercator
           JOIN (VALUES
             ('tertiary', 1),
             ('unclassified', 2),
             ('residential', 3),
             ('living_street', 4),
             ('service', 5),
             ('track', 6)
             ) AS v (highway, prio)
             ON v.highway=l.highway
         WHERE p.highway = 'turning_circle'
           OR p.highway = 'turning_loop'
         ORDER BY p.way, v.prio

 

Dla tego zapytania explain pokazuje:

 

Unique  (cost=677573.21..677587.30 rows=2819 width=76)
  ->  Sort  (cost=677573.21..677580.26 rows=2819 width=76)
        Sort Key: p.way, "*VALUES*".column2
        ->  Nested Loop  (cost=0.36..677411.67 rows=2819 width=76)
              ->  Hash Join  (cost=0.15..232445.42 rows=677063 width=274)
                    Hash Cond: (l.highway = "*VALUES*".column1)
                    ->  Seq Scan on planet_osm_line l  (cost=0.00..209587.65 rows=4289865 width=270)
                    ->  Hash  (cost=0.08..0.08 rows=6 width=36)
                          ->  Values Scan on "*VALUES*"  (cost=0.00..0.08 rows=6 width=36)
              ->  Index Scan using idx_highway_c_l on planet_osm_point p  (cost=0.21..0.65 rows=1 width=32)
                    Index Cond: (way && st_expand(l.way, '0.1'::double precision))
                    Filter: ((l.way && st_expand(way, '0.1'::double precision)) AND _st_dwithin(way, l.way, '0.1'::double precision))
(12 wierszy)

 

Dobrze rozumiem, że:

->  Seq Scan on planet_osm_line l  (cost=0.00..209587.65 rows=4289865 width=270)

postgres leci po 4 milionach rekordów z planet_osm_line? Tylko na co powinienem dać w takim wypadku indeks?

[Karister 1533]

Wg mnie:

 

Tak, robi full scan po planet_osm_line (zaaliasowane jako l), bo join jest po tym: ST_DWithin(p.way, l.way, 0.1). Postgres nie potrafi użyć indexu, jeśli na zaindexowanej kolumnie jest wywołana funkcja w zapytaniu, a ST_DWithin na taką mi wygląda. Klapa, index nie zostanie wykorzystany.

 

Ja bym spróbował stworzyć materialized view z potrzebnymi Ci kolumnami tabel oraz kolumnami, które będą wyliczone z funkcji, które obecnie są w SQLach. Wtedy nakładasz na nie (kolumny materialized view) indexy i w SQL zamiast funkcji używasz odpowiednich kolumn stworzonego widoku.

 

Indexujesz to, co jest w WHERE i ON w SQLach.

Edytowane 22 Sierpnia 2019 przez Karister