Nullwerte in PostgreSQL indexieren

Ich habe eine Abfrage in der form:

select m.id from mytable m
left outer join othertable o on o.m_id = m.id
    and o.col1 is not null and o.col2 is not null and o.col3 is not null
where o.id is null

Die Abfrage gibt ein paar hundert Datensätze, obwohl die Tabellen mit Millionen von Zeilen und es dauert ewig zu laufen (rund eine Stunde).

Wenn ich meine index-Statistiken verwenden:

select * from pg_stat_all_indexes
where schemaname <> 'pg_catalog' and (indexrelname like 'othertable_%' or indexrelname like 'mytable_%')

Sehe ich, dass nur der index für othertable.m_id verwendet wird, und dass die Indizes für col1..3 nicht benutzt. Warum ist das so?

Ich gelesen habe paar Ortedie PG hat traditionell nicht in der Lage gewesen, um den index NULL-Werte. Allerdings habe ich gelesen, dieser hat sich angeblich geändert, da PG 8.3? Ich bin derzeit mit PostgreSQL 8.4 auf Ubuntu 10.04. Brauche ich, um eine "Teil -" oder "funktional", index gezielt, um die Geschwindigkeit NICHT NULL Abfragen, oder ist es schon die Indizierung Null und ich bin nur Missverständnisse das problem?

InformationsquelleAutor der Frage Cerin | 2010-08-12

  1. 30

    Könnten Sie versuchen, einen partiellen index:

    CREATE INDEX idx_partial ON othertable (m_id)
WHERE (col1 is not null and col2 is not null and col3 is not null);

    Aus der docs: http://www.postgresql.org/docs/current/interactive/indexes-partial.html

    InformationsquelleAutor der Antwort Matthew Wood

  2. 5

    Partielle Indizes sind nicht gehen, um zu helfen Sie hier, wie Sie ' ll finden nur die Datensätze, die Sie nicht wollen. Sie einen index erstellen möchten, enthält die Datensätze, die Sie tun möchten. 

    CREATE INDEX findDaNulls ON othertable ((COALESCE(col1,col2,col3,'Empty')))
WHERE col1 IS NULL AND col2 IS NULL AND col3 IS NULL;

SELECT * 
FROM mytable m
JOIN othertable o ON m.id = o.m_id
WHERE COALESCE(col1,col2,col3,'Empty') = 'Empty';

    BTW die Suche nach ungültigen Links schließt sich in der Regel nicht so schnell, wie mit VORHANDEN oder NICHT VORHANDEN in Postgres.

    InformationsquelleAutor der Antwort Scott Bailey

  3. 1

    Einen einzelnen index auf m_id, col1, col2 und o ist.col3 wäre mein Erster Gedanke für diese Abfrage.

    Und verwenden ERKLÄREN auf diese Abfrage zu sehen, wie es ausgeführt wird, und was so lange dauert. Sie konnten zeigen uns die Ergebnisse, um Ihnen zu helfen.

    InformationsquelleAutor der Antwort Frank Heikens

  4. 1

    Einen partielle index scheint der richtige Weg hier:

    Wenn Sie eine Tabelle, die enthält
    fakturierte und nicht fakturierte Aufträge, wo die
    fakturierte Aufträge nehmen bis eine kleine
    Bruchteil der Gesamt-Tabelle und noch
    diese sind die am meisten zugegriffen Zeilen, Sie
    kann die Leistung verbessern, indem eine
    index sich nur auf die fakturierte Zeilen.

    Vielleicht nullwertfähige Spalten (col1,col2,col3) handeln, die in Ihrem Szenario als eine Art Flagge zu unterscheiden Unterklasse von Datensätzen in der Tabelle? (zum Beispiel, eine Art "logisches löschen") ? In diesem Fall, neben der partiellen index-Lösung, bevorzugen Sie vielleicht überdenken Sie Ihre design, und setzen Sie Sie in verschiedenen physischen Tabellen (vielleicht mit Vererbung), eine für die "live-Aufzeichnungen" andere für den "historischen Aufzeichnungen" und Zugriff auf den vollen Satz (nur bei Bedarf) über eine view.

    InformationsquelleAutor der Antwort leonbloy

  5. 0

    Haben Sie versuchen, erstellen Sie einen zusammengesetzten index auf othertable(m_id, col1, col2, col3)?

    Sollten Sie auch prüfen, die Ausführung planen (mit ERKLÄREN), sondern als die überprüfung der system-Tabellen für die index-Nutzung.

    PostgreSQL 9.0 (derzeit in beta) wird in der Lage sein zu verwenden und den index für eine IS NULL-Bedingung. Dieses feature wurde aufgeschoben

    InformationsquelleAutor der Antwort a_horse_with_no_name

Schreibe einen Kommentar