Index für eine Spalte finden

Hallo Forum,

ich möchte in Postgres 8.3 automatisiert Strukturinformationen sammeln. Allerdings würde ich gerne die Indexnamen auch den Spalten zuordnen. Dabei möchte ich wissen, ob eine konkrete Tabellenspalte einen oder mehrere Indexe hat.

In psql hilft bekanntlich

\di

Startet man psql mit der Option “-E”, wird psql gesprächig und zeigt das dazugehörige SQL-Statement:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as 
"Type",  
  r.rolname as "Owner",
 c2.relname as "Table",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i','')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Etwas abgewandelt kann man die Suche schon auf eine konkrete Tabelle begrenzen ($schema und $table sind nur Platzhalter):

SELECT n.nspname as "schema_name",
  c2.relname as "table_name",
  c.relname as "index_name"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind='i'
  AND n.nspname='$schema'
  AND c2.relname='$table'
ORDER BY 1,2;

In der Ausgabe erscheint der Schema- und Tabellenname, nicht jedoch der Spaltenname. Ein Pattern-Matching auf den Index-Namen ist keine Lösung, weil der Index-Name frei gewählt werden kann.

Ich habe schon den pg_catalog durchsucht, aber keine Tabelle gefunden, die die Spaltennamen der Tabellen enthält. Davon abgesehen weiß ich schon nicht, ob die Tabelle pg_class überhaupt Informationen über die Spalten enthält. Nach meiner laienhaften Vorstellung muß diese Information irgendwo hinterlegt sein. Woher sollte der Query Planner wissen, ob er einen Index befragen kann…?

Vielen Dank
ratio

Schon - aber was ist mit Indicies, die z. B. funktionale Ergebnisse speichern oder die mehrere Spalten einbeziehen? Was machst du mit denen?

@ads
Das ist eine Frage der Darstellung. Zunächst einmal würde ich gerne wissen, ob in der Tabelle pg_class bei Index-Einträgen ein Hinweis (vielleicht eine oid?) auf die Spalte(n) existiert. Dann bräuchte man noch eine Tabelle, die die Spaltennamen in Klartext und die oid enthält. Beides konnte ich bisher nicht finden.

Die Tabellen “pg_index” und “pg_indexes” hast du aber schon gesehen, oder?

Du suchst pg_index. Aber insbesondere indexprs und indpred richtig zu interpretieren überlasse ich Dir zur Übung :wink:

Andreas

@ads

pg_index habe ich gesehen und verwende ich bereits in meinem SQL-Statement.
pg_indexes hatte ich bei mir (mit pgAdmin3 1.10.0) noch nicht gesehen, auch nicht mit -> File -> Options -> Display -> Show System Objects. Jetzt nach Deinem Hinweis sehe ich sie in psql. In der Tabelle steht wiederum keine Spaltenname. Immerhin sieht man im Feld indexdef das CREATE INDEX Statement, was man parsen könnte, aber nicht gerade elegant wäre.


@akretschmer

Bei mir sind die Felder indexprs und indpred leer. Angesehen mit pgAdmin und psql…

Dann hast Du nur sehr einfache Indexe:

test=# create index idx_a on a((substring(room,1,5)));
CREATE INDEX
test=*# select indexprs from pg_catalog.pg_index where indexrelid = (SELECT oid FROM pg_class WHERE relname = 'idx_a');
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
indexprs | ({FUNCEXPR :funcid 936 :funcresulttype 25 :funcretset false :funcformat 0 :args ({FUNCEXPR :funcid 401 :funcresulttype 25 :funcretset false :funcformat 2 :args ({VAR :varno 1 :varattno 2 :vartype 1042 :vartypmod 36 :varlevelsup 0 :varnoold 1 :varoattno 2 :location 35}) :location -1} {CONST :consttype 23 :consttypmod -1 :constlen 4 :constbyval true :constisnull false :location 40 :constvalue 4 [ 1 0 0 0 ]} {CONST :consttype 23 :consttypmod -1 :constlen 4 :constbyval true :constisnull false :location 42 :constvalue 4 [ 5 0 0 0 ]}) :location 25})

Andreas

@akretschmer

Dann hast Du nur sehr einfache Indexe

Ja, ich habe bisher nur Indexe, die über eine Spalte erstellt wurden. Lassen sich in Postgres nur “nicht-einfache” Indexe identifizieren?

In Deinem Beispiel sprichst Du den Eintrag in pg_index auch über die oid an, und Du bekommst auch nicht den Spaltennamen oder eine Referenz darauf zurückgeliefert.

Scheint doch keine triviale Sache zu sein…

Nein.

In Deinem Beispiel sprichst Du den Eintrag in pg_index auch über die oid an, und Du bekommst auch nicht den Spaltennamen oder eine Referenz darauf zurückgeliefert.

pg_index enthält ja noch mehr Spalten, PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: pg_index erklärt, was diese bedeuten.


Scheint doch keine triviale Sache zu sein…[/QUOTE]

Scheint so, gell?


Andreas

@akretschmer

Danke für den Hinweis auf das Handbuch. Ich hatte die seite zwar schon gesehen, den wichtigsten Hinweis jedoch übersehen:

indkeyint2vector[url=http://www.postgresql.org/docs/8.4/interactive/catalog-pg-attribute.html]pg_attribute[/URL].attnum       This is an array of indnatts values that        indicate which table columns this index indexes.  For example a value        of 1 3 would mean that the first and the third table columns make up the index key. A zero in this array indicates that the corresponding index attribute is an expression over the table columns, rather than a simple column reference

Für Ein-Spalten-Indexe ist das Ergebnis leicht darstellbar, weil das cast nach ::varchar Identität hinsichtlich Datentyp und -wert herstellt:

SELECT n.nspname as "schema_name",
  c2.relname as "table_name",
  c.relname as "index_name",
  i.indkey as "col_nr",
  l.column_name as "column_name"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid ,
     information_schema.columns l
WHERE c.relkind='i'
  AND n.nspname=l.table_schema
  AND c2.relname=l.table_name
  AND l.ordinal_position::varchar = i.indkey::varchar
  AND n.nspname='$schema'
  AND c2.relname='$table'
ORDER BY 1,2,5,3;

@ads

Jetzt kommen wir auf Deine Frage zurück:

was ist mit Indicies, … die mehrere Spalten einbeziehen?

Gute Frage. Ich hatte bisher noch nicht mit dem Typ int2vector zu tun. Irgendeine Idee, wie man das ein SQL-Statement packen und “human readable” darstellen könnte? Kann man den int2vector (was ist das überhaupt?) anhand seiner Leerzeichen bereits in SQL splitten…?

Das ist ein interner Datentyp, nicht für ‘End-User’ bestimmt. Guggst Du dieses Kapitel durch: PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: BKI Backend Interface

Ansonsten ist die Doku dazu dürftig, guggst Du src/include/c.h :wink:

Andreas

Keine Ahnung. Ich wollte nie SQL Anfrageplaner spielen … :wink:

Spielen ist ein gutes Stichwort. Okay, ‘Anfrageplaner’ mag ich jetzt auch nicht grad spielen, ich begnüge mich mit int2vector:

test=# select '1 2 3 4'::int2vector;
 int2vector
------------
 1 2 3 4
(1 row)

test=*# select string_to_array('1 2 3 4'::int2vector::text,' ');
 string_to_array
-----------------
 {1,2,3,4}
(1 row)

test=*# select unnest(string_to_array('1 2 3 4'::int2vector::text,' '));
 unnest
--------
 1
 2
 3
 4
(4 rows)

Andreas

Ich habe mir mal das SQL Statement angesehen, welches der JDBC Treiber verwendet um diese Information zu lesen.

Wenn man das ein wenig ändert, kann man die Index-Definition recht schön mit folgendem Statement auslesen:

SELECT n.nspname AS table_schema,
       ct.relname AS table_name,
       ci.relname AS index_name,
       array_to_string(array_agg ( CASE
         WHEN i.indexprs IS NULL THEN a.attname
         ELSE pg_catalog.pg_get_indexdef(ci.oid,a.attnum,FALSE)
       END),', ') as index_expression,
       pg_catalog.pg_get_expr(i.indpred,i.indrelid) AS FILTER_CONDITION
FROM pg_catalog.pg_namespace n,
     pg_catalog.pg_class ct,
     pg_catalog.pg_class ci,
     pg_catalog.pg_attribute a,
     pg_catalog.pg_am am,
     pg_catalog.pg_index i
WHERE ct.oid = i.indrelid
AND   n.oid = ct.relnamespace
AND   n.nspname = 'public'
AND   ci.oid = i.indexrelid
AND   a.attrelid = ci.oid
AND   ci.relam = am.oid
AND   ct.relname = 'name_der_tabelle'
and   ci.relname = 'name_des_index'
group by table_schema, table_name, index_name, filter_condition;

Die Einschränkung auf einen bestimmten Index kann man natürlich auch weglassen um alle Indizes für eine Tabelle zu bekommen.

Ach, Ihr Glücklichen. Ihr seid schon auf 8.4 umgestiegen. Die Funktionen

array_agg()
unnest()
generate_series()

sind erst ab 8.4 verfügbar. Die Zutaten stehen jetzt bereit. Sobald ich auf 8.4 umgestiegen bin, gehe ich wieder in die Küche und werde das Rezept ausprobieren und über das Ergebnis berichten. Bis dahin werde ich pg_catalog_pg_indexes.indexdef parsen.

Vielen Dank
ratio

Für array_agg() gibt es in der Doku array_accum(), für unnest() kann man sich so behelfen:

-- split a string to rows, by David Fetter
CREATE OR REPLACE FUNCTION split_to_rows(TEXT, TEXT) /* Descriptive name */
RETURNS SETOF TEXT
STRICT
LANGUAGE sql
AS $$
    SELECT (string_to_array($1, $2))[s.i]
    FROM generate_series(
        1,
        array_upper(string_to_array($1, $2), 1)
    ) AS s(i);
$$;

und generate_series() gibt es schon deutlich länger als 8.4, IMHO schon seit 7.x.


Die richtigen Features von 8.4 sind gaaanz andere …


Andreas

Sorry, das unnest geht anders:

CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT LANGUAGE SQL AS $$SELECT $1[i] FROM generate_series(array_lower($1,1),array_upper($1,1)) i;$$;

Andreas

Sorry, irgendwie hatte ich meine Aufgabenstellung aus den Augen verloren. Das Ziel war es, diejenigen Indexe zu identifizieren, an denen eine gegebene Spalte “beteiligt” ist.

Mit dem folgenden Statement funktioniert es in 8.3 sowohl für Ein- als auch für Mehr-Spalten-Indexe:

SELECT n.nspname as schema_name,
  c2.relname as table_name,
  c.relname as index_name,
  i.indkey as col_nr,
  l.column_name as column_name,
  l.ordinal_position
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid ,
     information_schema.columns l
WHERE c.relkind='i'
  AND n.nspname=l.table_schema
  AND c2.relname=l.table_name
  AND ( i.indkey::text = l.ordinal_position::text OR i.indkey::text like '% '||l.ordinal_position::text OR i.indkey::text like l.ordinal_position::text||' %' OR i.indkey::text like '% '||l.ordinal_position::text||' %')
  AND n.nspname='$schema'
  AND c2.relname='$table'
  AND l.column_name='$column'
ORDER BY 1,2,5,3

Also nochmals vielen Dank für die vielen Denkanstöße!
ratio

musst du wahrscheinlich dein SQL anpassen.
Und für jede weitere PostgreSQL-Version auch.

Das ist der Nachteil, wenn man Systemtabellen verwendet, die dürfen sich verändern.

Ich komme nochmal auf das Thema zurück. Indexe finden hatten wir nun gelöst. Eine ähnliche Aufgabestellung hieß “Constraints finden”. Auch das ist inzwischen gelöst. Sofern es jemanden interessiert:

SELECT n.nspname as schema_name,
  c2.relname as table_name,
  l.column_name as column_name,
  t.conname as constraint_name,
  t.contype as constraint_type,
  CASE t.contype WHEN 'c' THEN 'CHECK' WHEN 'F' THEN 'FOREIGN KEY' WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END as constraint_type_name,
  t.conkey as col_arr,
  l.ordinal_position
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_constraint t ON t.conrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON t.conrelid = c2.oid ,
     information_schema.columns l
WHERE n.nspname=l.table_schema
  AND c2.relname=l.table_name
  AND arraycontains(t.conkey, CAST(ARRAY[l.ordinal_position::smallint] AS smallint[]))
  AND n.nspname='$schema'
  AND c2.relname='$table'
  AND l.column_name='$column'
ORDER BY 1,2,5,3

Jetzt stehe ich vor dem Problem, Sequenzen, die auch brav mit “ALTER SEQUENCE $table_$column_seq OWNED BY $table.$column” an bestimmte Spalten gebunden wurden, aufzuspüren. M.a.W: Ich möchte nun für ein/e gegebene/s Schema/Tabelle/Spalte diejenige Sequenz finden, die der Spalte gehört. Auf Pattern-Matching/string-Vergleich in der Tabelle pg_catalog.pg_class oder in der Tabelle information_schema.sequences (etwa in der Art “SELECT * FROM information_schema.sequences WHERE sequence_schema=’$schema’ AND sequence_name=’$table_$column_seq’;”) ist kein Verlass, weil die Namensgebung der Sequenzen nicht homogen sein muß.

Ein Indiz dafür, daß es irgendwo eine Referenz von einer Sequenz auf eine Spalte geben muß, liefert der Pgadmin, der nicht nur “nextval(sequence_name::regclass)” als Default-Wert anzeigt, sondern auch die Beziehung zwischen Spalte und Sequenz anzeigt, sobald man diese mit ALTER SEQUENCE … OWNED BY … hergestellt hat.

Vielen Dank
ratio