4. postgresQL 9.1.13: Lange Abfrage beschleunigen/korrigieren

postgresQL 9.1.13: Lange Abfrage beschleunigen/korrigieren


  • Hallo,

    in einer postgreSQL (9.1.13) db ich habe 3 db Tabellen, die über Fremdschlüssel miteinander verknüpft sind:

    Tabelle t1
 - id : Primärschlüssel
 - start_time : timestamp

Tabelle t2
 - id : Primärschlüssel
 - t1_id: Fremdschlüssel in t1

Tabelle t3
 - id : Primärschlüssel
 - t2_id: Fremdschlüssel in t2
 - name : text
 - value : text

    Alle Tabellen haben eine 1:n Beziehung, d.h. zu jeder Teile in t1 können beliebig viele Zeilen in t2 und für jeder Zeile in t2 können beliebig viele Zeilen in t3 existieren. Alle Primär- und Fremdschlüssel sind indiziert.

    Ich versuche nun, alle Zeilen aus t1 zu bekommen, für die Einträge mit bestimmten Werten in t3 existieren. Als Beispiel möchte ich alle Zeilen aus t1, die folgende Kriterien erfüllen:

    1. start_time muss zwischen '2016-04-20 00:00:00' und '2016-04-21 00:00:00' liegen
    2. in t3 muss eine Zeile existieren, die zur Zeile in t1 gehört und der name mit 'R02' beginnt
    3. in t3 muss eine Zeile existieren, die zur Zeile in t1 gehört und der name mit 'C43' beginnt
    4. in t3 muss eine Zeile existieren, die zur Zeile in t1 gehört und der name mit 'T556' beginnt

    Mein bisheriger Ansatz scheitert grandios 😉 Entweder liefern sie nicht das zurück, was sie sollen, oder die Abfrage läuft zu lange. Jede Zeile in t1 hat 5-8 Zeilen in t2 und jede Zeile in t2 hat 12-16 Zeilen in t3. Für jede Zeile in t1 existieren also zwischen 60 und 128 Zeilen in t3. Eine Test-Abfrage über t1 liefert 3600 Zeilen zurück und die in t3 müssen dafür 216.000 bis 460.800 Zeilen überprüft werden. Die Abfrage läuft knapp über 2 Minuten und liefert korrekt einen Treffer. Der Vergleich mit LIKE und Platzhaltern ist teuer, aber eine Laufzeiten über 2 Minuten scheint mir zu lang für diese Aufgabe. Ich bin kein SQL Fuchs, habe ich meine Abfrage zu doof formuliert und kann man sie iwie beschleunigen? Oder geschickter formulieren?

    SELECT 
	t.* 
FROM 
	t1 t
WHERE
   -- Filter über Zeitraum
   t.start_time	> '2016-04-20 00:00:00' AND t.start_time < '2016-04-21 00:00:00'
AND
(
   -- für alle Zeilen aus t prüfen, ob in t3 eine Zeile mit t1->t2->t3 existiert und t3.name = mit 'R02' beginnt
	EXISTS
	(
		SELECT 1 FROM 
			t2, t3
		WHERE
			t2.t1_id = t.id AND
			t2.id    = t3.t2_id AND
			t3.name  LIKE 'R02%'
	)
)
AND
(
	-- für alle Zeilen aus t prüfen, ob in t3 eine Zeile mit t1->t2->t3 existiert und t3.name = mit 'C43' beginnt
	EXISTS
	(
		SELECT 1 FROM
			t2, t3
		WHERE
			t2.t1_id = t.id AND
			t2.id    = t3.t2_id AND
			t3.name  LIKE 'C43%'
	)
)
AND
(
	-- für alle Zeilen aus t prüfen, ob in t3 eine Zeile mit t1->t2->t3 existiert und t3.name = mit 'T556' beginnt
	EXISTS
	(
		SELECT 1 FROM
			t2, t3
		WHERE
			t2.t1_id = t.id AND
			t2.id    = t3.t2_id AND
			t3.name  LIKE 'T556%'
	)
)

    Hier das EXPLAIN ANALYZE Ergebnis, falls da jemand schlau draus wird. Auf den ersten Blick fällt auf, dass ein Großteil der
    Zeit auf die Stringvergleiche in t3 entfallen. Aber dass die Abfrage jeweils 40 Sekunden in Stringvergleichen verbringt scheint
    mir suspekt.

    QUERY PLAN
Nested Loop Semi Join  (cost=3645464.50..10935177.81 rows=1 width=89) (actual time=120464.156..120480.900 rows=1 loops=1)
  Join Filter: (m.measurement_id = public.measurement_files.measurement_id)
  ->  Nested Loop Semi Join  (cost=3645464.50..7290561.61 rows=1 width=97) (actual time=98868.957..98885.700 rows=1 loops=1)
        Join Filter: (m.measurement_id = public.measurement_files.measurement_id)
        ->  Merge Semi Join  (cost=3645464.50..3645529.79 rows=4 width=93) (actual time=50552.796..50553.138 rows=12 loops=1)
              Merge Cond: (m.measurement_id = public.measurement_files.measurement_id)
              ->  Sort  (cost=424.68..434.57 rows=3955 width=89) (actual time=2.581..2.674 rows=3600 loops=1)
                    Sort Key: m.measurement_id
                    Sort Method: quicksort  Memory: 603kB
                    ->  Index Scan using idx_measurements_start_time on measurements m  (cost=0.00..188.38 rows=3955 width=89) (actual time=0.050..1.603 rows=3600 loops=1)
                          Index Cond: ((start_time > '2016-04-20 00:00:00'::timestamp without time zone) AND (start_time < '2016-04-21 00:00:00'::timestamp without time zone))
              ->  Sort  (cost=3645039.81..3645062.90 rows=9236 width=4) (actual time=50550.049..50550.094 rows=1875 loops=1)
                    Sort Key: public.measurement_files.measurement_id
                    Sort Method: quicksort  Memory: 1653kB
                    ->  Nested Loop  (cost=0.00..3644431.48 rows=9236 width=4) (actual time=8.227..50530.696 rows=18870 loops=1)
                          ->  Seq Scan on material_data  (cost=0.00..3521736.86 rows=9236 width=4) (actual time=7.634..44482.977 rows=18870 loops=1)
                                Filter: (material ~~ 'T556%'::text)
                          ->  Index Scan using pkey_measurement_files on measurement_files  (cost=0.00..13.27 rows=1 width=8) (actual time=0.318..0.318 rows=1 loops=18870)
                                Index Cond: (file_id = public.material_data.file_id)
        ->  Materialize  (cost=0.00..3644477.66 rows=9236 width=4) (actual time=0.011..4015.823 rows=177591 loops=12)
              ->  Nested Loop  (cost=0.00..3644431.48 rows=9236 width=4) (actual time=0.115..48070.211 rows=188337 loops=1)
                    ->  Seq Scan on material_data  (cost=0.00..3521736.86 rows=9236 width=4) (actual time=0.107..47111.984 rows=188337 loops=1)
                          Filter: (material ~~ 'C43%'::text)
                    ->  Index Scan using pkey_measurement_files on measurement_files  (cost=0.00..13.27 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=188337)
                          Index Cond: (file_id = public.material_data.file_id)
  ->  Materialize  (cost=0.00..3644477.66 rows=9236 width=4) (actual time=0.330..21577.020 rows=59343 loops=1)
        ->  Nested Loop  (cost=0.00..3644431.48 rows=9236 width=4) (actual time=0.327..21545.859 rows=59343 loops=1)
              ->  Seq Scan on material_data  (cost=0.00..3521736.86 rows=9236 width=4) (actual time=0.057..9803.926 rows=59343 loops=1)
                    Filter: (material ~~ 'R02%'::text)
              ->  Index Scan using pkey_measurement_files on measurement_files  (cost=0.00..13.27 rows=1 width=8) (actual time=0.196..0.196 rows=1 loops=59343)
                    Index Cond: (file_id = public.material_data.file_id)
Total runtime: 120484.287 ms
    0
  • R

    Versuch mal so in der Art:

    select * from (
    select t1 Join t2 Join t3 where Name like <kriterium1>
    Union
    select t1 Join t2 Join t3 where Name like <kriterium2>
    Union
    select t1 Join t2 Join t3 where Name like <kriterium3>
    )

    Syntax stimmt zwar nicht, aber das Prinzip sollte ersichtlich sein
    Ein Index auf t3.Name sollte nützlich sein.

    Die Sortiervorgänge sollten unbedingt eliminiert werden.
    Index-Scans ... möglicherweise unvermeidbar, kommt auf die Daten an.
    Bei wenigen Datensätzen ist das nicht unüblich.

    2 Minuten sind jedenfalls ca. 1 Minute und 58 Sekunden zu viel für die Abfrage.

    0

  • - between verwenden
    - substring statt like verwenden

    z.B.

    SELECT
    t.*
FROM
    t1 t
WHERE
   -- Filter über Zeitraum
   t.start_time between '2016-04-20' AND '2016-04-21'
AND t.id in
(
   select t1.id from t1, t2, t3
        WHERE
            t2.t1_id = t1.id AND
            t2.id    = t3.t2_id AND
            substring(t3.name for 3) = 'R02'
   union
   select t1.id from t1, t2, t3
        WHERE
            t2.t1_id = t1.id AND
            t2.id    = t3.t2_id AND
            substring(t3.name for 3) = 'C43'
   union
   select t1.id from t1, t2, t3
        WHERE
            t2.t1_id = t1.id AND
            t2.id    = t3.t2_id AND
            substring(t3.name for 4) = 'T556'
)

    Wenn dir die Performanz dann noch nicht ausreicht, wirf mal einen Blick auf die WITH Queries

    0

  • @Wutz
    Hast du jetzt nicht die ANDs in ORs verwandelt?

    0
Anmelden zum Antworten