Partial Foreign Key Index mit "not null"

Hallo,

ich habe mal ein paar Verständnisfragen. Wenn ich von einer Tabelle zur anderen durch ein Attribut joine, dann werden die Null-Wert-Rows eh nicht beachtet. Also würde doch ein Partial-Index für Foreign Keys reichen, oder?

Nur wenn ich des öfteren nach Null-Values selektiere macht ein voller Index darauf Sinn, stimmts?

Beispiel:

Create table a
( i integer,
j integer,
Primary key(i)
);

Create table b
( i integer,
j integer references a(i),
Primary key(i),
);

Create Index idx
ON b
USING btree
(j)
WHERE j is not null;

Bei folgendem würde der Partiale Index greifen:
select * from a
JOIN b ON b.j = a.i;

Würde mir bei folgender Abfrage ein kompletter Index weiterhelfen?

Select * from b WHERE b.j is null;

Irgendwie war mir so, als wenn Nulls bei Indexe anders behandelt werden.

Sind es B-Bäume oder B*-Bäume ist PostgreSQL? B* würde ja mehr Sinn machen.

Ich hab mal die Fragen zum besseren Überblick rot markiert.

Warum probierst Du das nicht mal aus? So eine Tabelle zu erstellen und mit Hilfe von generate_series() mit einer Million Zeilen zu füllen geht schneller als hier zu fragen…

test=*# explain select * from b where i is null;
                             QUERY PLAN
---------------------------------------------------------------------
 Bitmap Heap Scan on b  (cost=4.29..16.84 rows=5 width=4)
   Recheck Cond: (i IS NULL)
   ->  Bitmap Index Scan on b_voll  (cost=0.00..4.29 rows=5 width=0)
         Index Cond: (i IS NULL)
(4 Zeilen)

Zeit: 0,549 ms
test=*# explain select * from b where i = 20;
                               QUERY PLAN
------------------------------------------------------------------------
 Bitmap Heap Scan on b  (cost=4.29..16.85 rows=5 width=4)
   Recheck Cond: (i = 20)
   ->  Bitmap Index Scan on b_notnull  (cost=0.00..4.29 rows=5 width=0)
         Index Cond: (i = 20)
(4 Zeilen)

Zeit: 0,446 ms
test=*# \d b
      Tabelle »public.b«
 Spalte |   Typ   | Attribute
--------+---------+-----------
 i      | integer |
Indexe:
    »b_notnull« btree (i) WHERE i IS NOT NULL
    »b_voll« btree (i)

Andreas

generate_series() Die Funktion kannte ich noch nicht :slight_smile:

Ich hab nun ein paar Tests gemacht, und festgestellt, dass er keine Indexe benutzt wenn ich auf “is null” selektiere.

Also sind demnach Foreign Keys die nicht per Partial Index (not null) benutzen unnötig groß, weil die Nulls eh nicht benutzt werden, richtig?

Hier mein Testablauf mit Ergebnissen:

–drop table a;
Create temp table a
( i integer,
j integer,
Primary key(i)
);
–drop table b;
Create temp table b
( i integer,
j integer references a(i),
Primary key(i)
);
insert into a VALUES (generate_series(1,2000000),generate_series(1,2000000));
insert into b VALUES (generate_series(1,2000000),generate_series(1,2000000));
insert into b VALUES (generate_series(2000001,6000000),null);
analyse a;
analyse b;
explain select * from a
JOIN b ON b.j = a.i;
Ergebnis:
“Hash Join (cost=196733.36…302538.93 rows=616639 width=16)”
" Hash Cond: (a.i = b.j)"
" -> Seq Scan on a (cost=0.00…30810.09 rows=1999909 width=8)"
" -> Hash (cost=92434.05…92434.05 rows=6000105 width=8)"
" -> Seq Scan on b (cost=0.00…92434.05 rows=6000105 width=8)"
explain Select * from b WHERE b.j is null;
Ergebnis: “Seq Scan on b (cost=0.00…92434.05 rows=4150073 width=8)”
Create Index idx_not_null
ON b
USING btree
(j)
WHERE j is not null;
Create Index idx_full
ON b
USING btree
(j);
Create Index idx_null
ON b
USING btree
(j)
WHERE j is null;
analyse a;
analyse b;
explain select * from a
JOIN b ON b.j = a.i;
Ergebnis:
“Merge Join (cost=0.00…142170.67 rows=666678 width=16)”
" Merge Cond: (a.i = b.j)"
" -> Index Scan using a_pkey on a (cost=0.00…62758.99 rows=2000035 width=8)"
" -> Index Scan using idx_full on b (cost=0.00…188254.48 rows=6000105 width=8)"

explain Select * from b WHERE b.j is null;
Ergebnis:
“Seq Scan on b (cost=0.00…92434.05 rows=4000070 width=8)”
" Filter: (j IS NULL)"

select pg_relation_size(‘idx_null’)
Ergebnis: 89866240
select pg_relation_size(‘idx_full’)
Ergebnis: 134791168
select pg_relation_size(‘idx_not_null’)
Ergebnis: 44949504

Nun aber. Ist übrigens überaus praktisch, es gibt viele sehr interessante Anwendungen dafür…

Ich hab nun ein paar Tests gemacht, und festgestellt, dass er keine Indexe benutzt wenn ich auf “is null” selektiere.

Doch, er kann auch da Indexe nutzen…

Also sind demnach Foreign Keys die nicht per Partial Index (not null) benutzen unnötig groß, weil die Nulls eh nicht benutzt werden, richtig?

Versteh ich jetzt grad nicht wirklich, was Du sagen willst. Ob Indexe genutzt werden und auch wie (Indexscan, Bitmap Index Scan) hängt von vielen Dingen ab, u.a. auch von der ‘correlation’, also wie logische und physikalische Sortierung übereinstimmen. Du hast erst viele Daten eingegeben und dann die Nullen, damit ist das nicht gemischt verteilt.

Im übrigen, wenn Du in einer Spalte, die einen FK darstellt, einen hohen Anteil an NULL hast, dann vermute ich, stimmt was anderes nicht.

Andreas

Wenn es sich um B*-Bäume handelt, dann müsste es doch immer von Vorteil sein, wenn das erste Element durch das abfragen des Baums angesteuert wird, und danach durch die übliche Blattverknüpfung die Datensätze sequenziell abgearbeitet werden.

Nur weil die Daten an einem Stück liegen, ist es doch unsinnig ein sequenziellen Scan über 6 Millionen Werte durchzuführen. Mit der oben beschriebenen Methode wären es 4 Millionen + einmal Baum durchhangeln.

Ich werd heut Nacht nochmal schauen wie es ist, wenn ich die Daten gemischt einfüge.