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.
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?
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.