Znaleźć tabele PostGIS, w których brakuje indeksów?

10

Ostatnio się rozglądałem pg_stat_user_tablesi byłem zaskoczony, widząc dużą liczbę sekwencyjnych skanów na niektórych moich tabelach przestrzennych. Rzeczywiście, w tych tabelach brakowało indeksów przestrzennych.

Jak znaleźć wszystkie tabele z nieindeksowaną kolumną geometrii?

postgis postgresql

— dbaston
źródło

Dzięki za odwołanie do pg_stat_user_tables. To pocieszające, że ktoś z twoją wiedzą przyznaje się do takich błędów. Młodym w pracy, których mentoruję, zawsze mówię: Jeśli nie ma naturalnego kandydata na klucz podstawowy, dodaj kolumnę szeregową. Zawsze określaj SRID i typ geometrii. Zawsze dodawaj indeks przestrzenny. Ponieważ skany sekwencji mogą działać z milionem wierszy, ale przychodzi moment ..... Rób, co mówię, a nie tak jak ja: D.

— John Powell,

9

Tabele z brakującymi indeksami przestrzennymi można znaleźć, sprawdzając tabele systemowe:

SELECT g.* 
FROM 
  (SELECT 
     n.nspname, 
     c.relname, 
     c.oid AS relid, 
     a.attname, 
     a.attnum 
   FROM pg_attribute a 
   INNER JOIN pg_class c ON (a.attrelid=c.oid)
   INNER JOIN pg_type t ON (a.atttypid=t.oid)
   INNER JOIN pg_namespace n ON (c.relnamespace=n.oid) 
   WHERE t.typname='geometry' 
   AND   c.relkind='r'
 ) g 
LEFT JOIN pg_index i ON (g.relid = i.indrelid AND g.attnum = ANY(i.indkey)) 
WHERE i IS NULL;

— dbaston
źródło

Czy może być jeszcze lepiej WHERE t.typname IN ('geometry', 'geography') AND t.typtype='b'? Zobacz trac.osgeo.org/gdal/ticket/6896 .

— user30184

@ user30184 Czy możesz to wyjaśnić t.typtype = 'b'?

— dbaston

1

To właściwie bezużyteczny kawałek. Zmiana kodu w GDAL dotyczyła rzadkiej sytuacji, gdy standardowa baza danych PostgreSQL ma tabelę o nazwie „geometria”. To także ma wpis w pg_type, ale z typtype = 'c'. Jeśli jednak masz zainstalowany PostGIS, nie jest możliwe zakończenie takiej sytuacji. create table "geometry" (foo text);daje

ERROR:  type "geometry" already exists HINT:  A relation has an associated type of the same name, so you must use a name that doesn't conflict with any existing type.

— użytkownik30184

6

Stworzyłem funkcję, która może automatycznie tworzyć wszystkie brakujące indeksy. Parametr „symuluj” pozwala uzyskać listę brakujących indeksów przestrzennych, ale nie wykonuje operacji UTWÓRZ INDEKS

Zobacz https://gist.github.com/mdouchin/cfa0e37058bcf102ed490bc59d762042

Aby uzyskać listę brakujących indeksów, uruchom:

SELECT * FROM create_missing_spatial_indexes(True)

Aby utworzyć potrzebne indeksy, uruchom:

SELECT * FROM create_missing_spatial_indexes()

lub

SELECT * FROM create_missing_spatial_indexes(False)

— użytkownik779641
źródło

Działa jak urok! Świetne narzędzie.

— RyanKDalton