Forum PCLab.pl: potrzebna pomoc przy indeksach w postgresie - Forum PCLab.pl

Skocz do zawartości

Otwarty

Ikona Ostatnio dodane tematy

Ikona Najnowsze pliki

Strona 1 z 1
  • Nie możesz rozpocząć nowego tematu
  • Nie możesz odpowiadać w tym temacie

potrzebna pomoc przy indeksach w postgresie Oceń temat: -----

#1 Użytkownik jest niedostępny   januzi 

  • ^ patryjota, katolig, bochater
  • Ikona
  • Grupa: Moderatorzy
  • Postów: 35653
  • Dołączył: Nd, 08 Cze 03

Napisany 18 Sierpień 2019 - 17:41

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)


#2 Użytkownik jest niedostępny   Karister 

  • Gaduła
  • PipPipPip
  • Grupa: Forumowicze
  • Postów: 448
  • Dołączył: Nd, 14 Wrz 08

Napisany 19 Sierpień 2019 - 00:15

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.

#3 Użytkownik jest niedostępny   januzi 

  • ^ patryjota, katolig, bochater
  • Ikona
  • Grupa: Moderatorzy
  • Postów: 35653
  • Dołączył: Nd, 08 Cze 03

Napisany 19 Sierpień 2019 - 15:15

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.

#4 Użytkownik jest niedostępny   Karister 

  • Gaduła
  • PipPipPip
  • Grupa: Forumowicze
  • Postów: 448
  • Dołączył: Nd, 14 Wrz 08

Napisany 19 Sierpień 2019 - 23:10

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ą.

Ten post był edytowany przez Karister dnia: 19 Sierpień 2019 - 23:14


#5 Użytkownik jest niedostępny   januzi 

  • ^ patryjota, katolig, bochater
  • Ikona
  • Grupa: Moderatorzy
  • Postów: 35653
  • Dołączył: Nd, 08 Cze 03

Napisany 19 Sierpień 2019 - 23:30

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?

#6 Użytkownik jest niedostępny   Karister 

  • Gaduła
  • PipPipPip
  • Grupa: Forumowicze
  • Postów: 448
  • Dołączył: Nd, 14 Wrz 08

Napisany 22 Sierpień 2019 - 18:21

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.

Ten post był edytowany przez Karister dnia: 22 Sierpień 2019 - 18:24


Strona 1 z 1
  • Nie możesz rozpocząć nowego tematu
  • Nie możesz odpowiadać w tym temacie

1 Użytkowników czyta ten temat
0 użytkowników, 1 gości, 0 anonimowych