Skocz do zawartości
januzi

potrzebna pomoc przy indeksach w postgresie

Rekomendowane odpowiedzi

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)

Udostępnij tę odpowiedź


Odnośnik do odpowiedzi
Udostępnij na innych stronach

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.

Udostępnij tę odpowiedź


Odnośnik do odpowiedzi
Udostępnij na innych stronach

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.

Udostępnij tę odpowiedź


Odnośnik do odpowiedzi
Udostępnij na innych stronach

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 przez Karister

Udostępnij tę odpowiedź


Odnośnik do odpowiedzi
Udostępnij na innych stronach

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?

Udostępnij tę odpowiedź


Odnośnik do odpowiedzi
Udostępnij na innych stronach

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 przez Karister

Udostępnij tę odpowiedź


Odnośnik do odpowiedzi
Udostępnij na innych stronach

Jeśli chcesz dodać odpowiedź, zaloguj się lub zarejestruj nowe konto

Jedynie zarejestrowani użytkownicy mogą komentować zawartość tej strony.

Zarejestruj nowe konto

Załóż nowe konto. To bardzo proste!

Zarejestruj się

Zaloguj się

Posiadasz już konto? Zaloguj się poniżej.

Zaloguj się

  • Ostatnio przeglądający   0 użytkowników

    Brak zarejestrowanych użytkowników przeglądających tę stronę.

  • Tematy

  • Odpowiedzi

    • Ale mi ciśnienie podniósł durny babol  W pewnym zadaniu po odszukaniu części należy wykonać prototyp z technologią z zamierzchłych czasów ale nie mogłem zrobić bo NPC z zadania stanął mi przy warsztacie i musiałem fast-travelem iść gdzie indziej sobie uklepać Co za głupota cały stół obleciałem to się nie da bo baran tak mi się ustawił... nosz k....   
    • Przejściowo można, jako obniżenie emisji, ale docelowo do osiągnięcia net zero to już nie. Trzeba będzie wynaleźć jakieś tanie i powszechnie technologie magazynowania albo wychwytu,
    • Karta 3070 8GB nie ma 6GB. Chłodzenie średnie, ale jak zrobisz UV procesora to podejrzewam, że wystarczy do utrzymania przyzwoitych warunków pracy.
    • DG2 to kopia jedynki, w jedynce mam więcej przegrane niż w DG2, i widzę, że to ładniejsza" kopia, plus znerfione kilka elementów z jedynki, ale nie wiem zna stanu gry na premierę, czy np eternal ferrystone było, czy wprowadzili z czasem, jest też normalna jego wersja, w porównaniu do dwójki, w jedynce dawaj nieograniczony teleport do miejsca gdzie odkryliśmy, lub postawiliśmy portal- ten portal w dwójce jest w mikropłatnościach, w jedynce mam w cholerę kamieni teleportacji do przywołania pionków, w dwójce, widzę, że w tym samym czasie co w jedynce zdobywam go dużo wolniej i mniej, ale obstawiam, że podnieśli poziom trudności plus dodali opcję kupienia tego w mikropłatnościach, stąd jest go bardzo mało. Jak się podoba jedynka to i dwójka się spodoba, mi się podoba, jest dużo bardziej urozmaicona, mimo tego cyrku z DLC, ale one nie muszę wpływać na naszą grę, gra jest tak stworzona, jedynce było to samo, tylko jak piszę, pewne elementy są znerfione i ograniczone, zapewne pod próbę wciskania graczowi mikropłatności, zapewne to trafne moje domysły i nie tylko. Gra ma kilka mankamentów, jak prawie w kółko walczymy z tymi samymi, w jedynce to samo, walki, gobliny, bandyci, o cyklop, troll i koniec, czasem harpie, w dwójce to samo, plus kilka nowych zagrożeń, ale w dwójce jestem na początku przygody. Nie wiem co Kiszak opowiada, bo nie chcę sobie spoilerować, ale podglądałem jego wcześniejsze wywody z gry, i cóż ciężko się nie zgodzić z kilkoma rzeczami, ale były one już podawane w temacie.
    • I dobrze bo ER jest świetny.Podziwiam skila i wytrwałość ludzi pokonujących takich bossów bez lvl postaci
  • Aktywni użytkownicy

×
×
  • Dodaj nową pozycję...