Mag vielleicht trivial sein, aber ich bin gestern wieder über einen relativ häufigen Optimierungsfall gestolpert, der zumindest in der Entwicklungsphase nicht immer so einfach zu Tage tritt:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT a.article_id , a.headline , a.images FROM babel_videogameszone.article a , public.MAIN_DVD b WHERE a.entity_id = b.id AND a.article_type_id in (1,2,5,6,34,38,41) AND checkRegex(a.images,'µ.*?µ') ORDER BY a.published desc LIMIT 4 |
Das Query läuft ca. 1,3 Sekunden – nicht gut. Was passiert hier?
Hier ist der Ausführungsplan:
|
1 2 3 4 5 6 7 8 9 10 11 |
Limit (cost=0.00..13.58 rows=4 width=373) (actual time=244.332..1322.842 rows=1 loops=1) -> Nested Loop (cost=0.00..64719.15 rows=19065 width=373) (actual time=244.331..1322.840 rows=1 loops=1) -> Index Scan Backward using idx05_article on article a (cost=0.00..59195.69 rows=19131 width=377) (actual time=0.094..1221.892 rows=40665 loops=1 ) Filter: ((article_type_id = ANY ('{1,2,5,6,34,38,41}'::integer[])) AND checkregex(images, 'µ.*?µ'::text)) Rows Removed by Filter: 31541 -> Index Only Scan using idx1_dvd on main_dvd b (cost=0.00..0.28 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=40665) Index Cond: (id = a.entity_id) Heap Fetches: 0 Total runtime: 1322.966 ms (9 rows) |
Die meiste Zeit wird im Index Scan Backward auf idx05_article der article-Tabelle verbracht (Zeile 3) – das ist der Index auf das published-Feld. Seltsamerweise ist das gleiche Query mit einem anderen Entitätstyp pfeilschnell:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT a.article_id , a.headline , a.images FROM babel_videogameszone.article a , public.MAIN_PC_GAME b WHERE a.entity_id = b.id AND a.article_type_id in (1,2,5,6,34,38,41) AND checkRegex(a.images,'µ.*?µ') ORDER BY a.published desc LIMIT 4 |
Ausführungsplan:
|
1 2 3 4 5 6 7 8 9 10 |
Limit (cost=0.00..13.58 rows=4 width=373) (actual time=0.067..0.320 rows=4 loops=1) -> Nested Loop (cost=0.00..64746.21 rows=19065 width=373) (actual time=0.067..0.319 rows=4 loops=1) -> Index Scan Backward using idx05_article on article a (cost=0.00..59195.69 rows=19131 width=377) (actual time=0.047..0.251 rows=9 loops=1) Filter: ((article_type_id = ANY ('{1,2,5,6,34,38,41}'::integer[])) AND checkregex(images, 'µ.*?µ'::text)) Rows Removed by Filter: 10 -> Index Only Scan using idx1_pc_game on main_pc_game b (cost=0.00..0.28 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=9) Index Cond: (id = a.entity_id) Heap Fetches: 0 Total runtime: 0.351 ms (9 rows) |
Der Sinn des JOINs auf die jeweilige Entitätstabelle ist im Beispiel nicht offensichtlich, da hier ja nur Felder aus der article-Tabelle genutzt werden – es kann jedoch Fälle geben, in denen auch Daten aus der Entitätstabelle benötigt werden, insofern brauchen wir den JOIN hier.
Das Problem wird aber offensichtlich, wenn man Zeile 6 im ersten mit Zeile 5 im zweiten Ausführungsplan vergleicht – im ersten Fall werden nach dem Index-Scan 31.541 Tupel durch den Filter verworfen, im zweiten gerade mal 10.
Die Ursache findet man, wenn man die Datenlage kennt: Über viele Produktarten wird auf Videogameszone.de regelmäßig berichtet, so auch über PC-Spiele. Dementsprechend muss die Datenbank beim zweiten Query auch nicht lange über die Daten rennen, bis sie die gewünschten vier Datensätze zusammen hat. 10.167 von 72.210 Artikeln haben ein PC-Spiel als-Hauptthema.
Das erste Query ist aber für einen eher exotischen Fall – es gibt praktisch keine Artikel zu DVDs auf Videogameszone, exakt drei an der Zahl (in einer Gesamtmenge von wie gesagt 72.210). Das führt hier dazu, dass die Datenbank praktisch den gesamten Published-Index auf Treffer überprüfen muss – und am Ende feststellt, dass nicht einmal die gewünschten vier Zeilen zusammengekommen sind. Die Kardinalität für den DVD-Entitätstyp ist also sehr niedrig, was die Ausführungszeit der Abfrage in diesem Fall explodieren lässt.
Beim Entwickeln ist das offensichtlich nicht aufgefallen, da ausschließlich Spiele-Entitäten überprüft wurden. Nun enthält aber die article-Tabelle neben der entity_id der Haupt-Entität auch deren entity_type_id – und für die gibt es auch einen Index. Also fügen wir die Bedingung für die entity_type_id hinzu:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT a.article_id , a.headline , a.images FROM babel_videogameszone.article a , public.MAIN_DVD b WHERE a.entity_id = b.id AND a.entity_type_id = 183 AND a.article_type_id in (1,2,5,6,34,38,41) AND checkRegex(a.images,'µ.*?µ') ORDER BY a.published desc LIMIT 4 |
Der Query-Planer weiß aus den Tabellen-Statistiken, dass die Selektivität für die Entity-Type-ID 183 sehr hoch ist, daher wählt er einen Ausführungsplan, der den passenden Index auf dieses Feld nutzen kann:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Limit (cost=898.53..898.54 rows=4 width=373) (actual time=0.129..0.129 rows=1 loops=1) -> Sort (cost=898.53..898.66 rows=52 width=373) (actual time=0.129..0.129 rows=1 loops=1) Sort Key: a.published Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=5.74..897.75 rows=52 width=373) (actual time=0.078..0.096 rows=1 loops=1) -> Bitmap Heap Scan on article a (cost=5.74..763.12 rows=52 width=377) (actual time=0.058..0.075 rows=1 loops=1) Recheck Cond: (entity_type_id = 183) Filter: ((article_type_id = ANY ('{1,2,5,6,34,38,41}'::integer[])) AND checkregex(images, 'µ.*?µ'::text)) Rows Removed by Filter: 2 -> Bitmap Index Scan on idx_article_site_entity_type (cost=0.00..5.72 rows=195 width=0) (actual time=0.021..0.021 rows=3 loops=1) Index Cond: (entity_type_id = 183) -> Index Only Scan using idx1_dvd on main_dvd b (cost=0.00..2.58 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1) Index Cond: (id = a.entity_id) Heap Fetches: 0 Total runtime: 0.168 ms (15 rows) |
Wir sind von 1322,966ms runter auf 0,168ms, das entspricht einem Faktor von siebentausendachthundertfünfundsiebzig (7.875). Ziel erreicht, würde ich sagen!
Das zweite Query (PC-Spiel) wird dadurch allerdings nicht schneller – der Query-Planer bevorzugt für die PC-Spiele-Entität den bisherigen Ausführungsplan; er kennt offensichtlich die Kardinalität des Wertes der entity_type_id für PC Game (viele, viele) und damit die schlechte Selektivität des entity_type_id-Index für diesen Fall und bevorzugt daher den alternativen Plan, der eine höhere Selektivität bringen könnte:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT a.article_id , a.headline , a.images FROM babel_videogameszone.article a , public.MAIN_PC_GAME b WHERE a.entity_id = b.id AND a.entity_type_id = 217 AND a.article_type_id in (1,2,5,6,34,38,41) AND checkRegex(a.images,'µ.*?µ') ORDER BY a.published desc LIMIT 4 |
|
1 2 3 4 5 6 7 8 9 10 |
Limit (cost=0.00..88.66 rows=4 width=373) (actual time=0.076..0.185 rows=4 loops=1) -> Nested Loop (cost=0.00..60308.86 rows=2721 width=373) (actual time=0.075..0.183 rows=4 loops=1) -> Index Scan Backward using idx05_article on article a (cost=0.00..59376.20 rows=2730 width=377) (actual time=0.056..0.141 rows=4 loops=1) Filter: ((entity_type_id = 217) AND (article_type_id = ANY ('{1,2,5,6,34,38,41}'::integer[])) AND checkregex(images, 'µ.*?µ'::text)) Rows Removed by Filter: 16 -> Index Only Scan using idx1_pc_game on main_pc_game b (cost=0.00..0.33 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=4) Index Cond: (id = a.entity_id) Heap Fetches: 0 Total runtime: 0.213 ms (9 rows) |
Das Hinzufügen der WHERE-Bedingung auf die entity_type_id schadet hier aber auch nicht. Somit haben wir durch eine sehr kleine Änderung eine konsistent massiv verbesserte Ausführungsgeschwindigkeit bekommen.
P.S.: Schöner wäre der JOIN natürlich mit echtem JOIN …
|
1 2 3 4 5 6 7 8 9 10 11 |
EXPLAIN ANALYZE SELECT a.article_id , a.headline , a.images FROM babel_videogameszone.article a JOIN public.MAIN_PC_GAME b ON (a.entity_id = b.id) WHERE a.entity_type_id = 217 AND a.article_type_id in (1,2,5,6,34,38,41) AND checkRegex(a.images,'µ.*?µ') ORDER BY a.published desc LIMIT 4 |
… dem Query-Planer ist das aber egal, der Ausführungsplan ist für beide Varianten der gleiche.