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ż to piękne co się dzieje. Droga do delegalizacji pis otwarta jak nigdy
    • Jak obudowa ci się tak podoba to bierz bo to ostatnie sztuki i zaraz będzie niedostępna. Zasilacze Gigabyte w zdecydowanej większości nie są udane, stąd ich niskie ceny. XPG Core Reactor II 850W za 490zł: https://lp.morele.net/gaming-promocja/?kategoria=Zasilacze+komputerowe Dysk albo odpowiednik Lexara tyle że od polskiej firmy: https://www.ceneo.pl/160503416 Albo ADATA z DRAMem za 1300zł: https://www.ceneo.pl/150752709 Chłodzenie - Navis F360 jest wydajniejszy i potencjalnie mniej awaryjny niż MSI: https://www.ceneo.pl/148968467 Ale to i tak głównie dla wyglądu, dla 7800X3D wystarczy chłodzenie powietrzne za 220zł. PS. 8 rdzeni to nie za mało do stawiania maszyn wirtualnych? W cenie zwykłej 4080 możesz mieć cichą 4080 Super: https://www.ceneo.pl/163671313 Wiem że różnica jest praktycznie żadna, ale nowszy model to nowszy model. MSI B650 Gaming Plus to solidna płyta z dobra sekcją zasilania i niezłym wyposażeniem jeśli chodzi o złącza, ale - ma tylko podstawowy chip audio oraz wspiera wyłącznie PCI-E 4 zarówno dla karty graficznej (4090 dalej korzysta z PCI-E 4), jak i dla dysku.
    • Tak sobie podsumowuje jakie co ma wady. Poprawcie mnie jeśli coś źle zrobiłem. Mamy QD-OLEDy: ASUS - firmware, brzydka nóżka, problemy z HDR? MSI - brak aktualizacji, cable management też kuleje, nóżka jako taka, dioda DELL - zakrzywiony ( dla mnie wada ) Gigabyte - nie wiem, dostępność   WOLED  LG - Dual Mode tylko mnie interesował, bo i tak matowy ( - ) i jak już mat to z Dual Mode dla którego miałbym zastosowanie, Plus pivot by mi się przydał ale to bardziej miły dodatek. No i wizualnie mi pasuje najbardziej. Tylko ten mat, chyba wszystko inne bym przebolał ale z tego co ten Display Guy na swoim kanale głosi to mam ogromne wątpliwości. Rozumiem że to bardziej gamingowy monitor i odblask by się nie sprawdził ale też super profesjonalny to on nie jest, tu raczej najnowszy Zowie 540Hz były lepszy.
    • 5min temu byl maly update. https://steamdb.info/app/2420110/patchnotes/ Ale nie podali nic poki co.
    • W DE może i zarabiają więcej ale przynajmniej mają OLEDy taniej Nawet 83" C3 2458eur ~10585zł  https://www.mydealz.de/deals/83-lg-4k-oled-evo-tv-c3-120-hz-cb-2338840
  • Aktywni użytkownicy

×
×
  • Dodaj nową pozycję...