primary key und index

Moin alle miteinander.

Wenn ich das untenstehende explain richtig deute, dann wird der primary key (bestehend aus host und username bei users bzw. host und filename bei files) nicht benutzt, um die Auswertung zu fahren. Sehe ich das richtig? Muss ich das Feld host explizit nochmal bei beiden Tabellen (users und files) indexieren ?

files.uid und users.uid sind indexiert.

files >20 Mio datensätze
users ca. 100000 Datensätze

Beste Grüße von nem pg-Neuling

Jörn (für jede Optimierung dankbar)


$ psql -c "explain select users.* from users left outer join files on files.uid
=users.uid and files.host=users.host WHERE (users.host='deedcaskfi506' and file
s.file is null);" EDS
                                          QUERY PLAN

--------------------------------------------------------------------------------
--------------
 Merge Left Join  (cost=20572918.61..20651731.49 rows=1888 width=164)
   Merge Cond: (("outer"."?column14?" = "inner"."?column4?") AND ("outer".uid =
"inner".uid))
   Filter: ("inner".file IS NULL)
   ->  Sort  (cost=5320.61..5325.33 rows=1888 width=164)
         Sort Key: (users.host)::text, users.uid
         ->  Index Scan using userkey on users  (cost=0.00..5217.88 rows=1888 wi
dth=164)
               Index Cond: ((host)::text = 'deedcaskfi506'::text)
   ->  Sort  (cost=20567598.00..20593738.56 rows=10456224 width=543)
         Sort Key: (files.host)::text, files.uid
         ->  Seq Scan on files  (cost=0.00..975914.24 rows=10456224 width=543)
(10 rows)

Da du auch auf host vergleichst, stellt die Datenbank fest, das sie sowieso die gesamte Tabelle durchlaufen muss, also ist ein Index bei den uids fast uninteressant.

Ansonsten: hast du Vacuum analyze laufen lassen?

Ja, aber sehe ich es richtig, das er die Teile des mehrteiligen Primary Keys nicht einzeln indexiert und damit auch nicht zum Suchen heranzieht, wenn nur nach Teilen gesucht wird?

DB2 macht das etwas anders.

Gruss und so

Jörn (etwas verwöhnt, eventl)

Den mehrteiligen Key wird er meines Wissens nach nicht nutzen, nein.

so, ein klein wenig besser sieht es schon aus, aber er will, trotz host-indexierung immer noch files.host sequentiell abarbeiten, und das ist bei 22 Mio Zeilen doch mühsam.

Hat noch jemand eine Idee, warum der den key für files.host nicht nehmen will? Ich hätte auch nichts dagegen, wenn er users.hosts (nur 100000) durcharbeitet, aber dafür den files.host key nutzt.

$ psql -c "explain select users.* from users left outer join files on files.uid
=users.uid and users.host=files.host WHERE (users.host='deedcaskfi506' and file
s.file is null);" EDS
                                          QUERY PLAN

--------------------------------------------------------------------------------
--------------
 Merge Left Join  (cost=12784018.16..12961937.93 rows=1888 width=164)
   Merge Cond: (("outer"."?column14?" = "inner"."?column4?") AND ("outer".uid =
"inner".uid))
   Filter: ("inner".file IS NULL)
   ->  Sort  (cost=4088.77..4093.49 rows=1888 width=164)
         Sort Key: (users.host)::text, users.uid
         ->  Index Scan using uhostkey on users  (cost=0.00..3986.04 rows=1888 w
idth=164)
               Index Cond: ((host)::text = 'deedcaskfi506'::text)
   ->  Sort  (cost=12779929.39..12836907.50 rows=22791242 width=90)
         Sort Key: (files.host)::text, files.uid
         ->  Seq Scan on files  (cost=0.00..1099264.42 rows=22791242 width=90)
(10 rows)

Welche Version hast Du? Hab grad nicht wirklich Zeit und Lust, aber mit 8.1 und Indexen auf den einzelnen Spalten (keine Verbundindexe) dürfte er Bitmap Index Scan wählen.

Andreas