Performanz

Hallo zusammen,

ich hoffe irgendjemand kann mir bei meinen Performanzproblemen helfen.
Trotz Indizes auf allen Spalten, die im Where clause auftreten bekomme ich folgenden ziemlich schlechten explain analyze output:
Sort (cost=92911.05…92912.91 rows=743 width=36) (actual time=22628.564…22634.115 rows=15059 loops=1)
Sort Key: b.snp_id
-> Hash Join (cost=481.81…92875.62 rows=743 width=36) (actual time=5532.788…22528.250 rows=15059 loops=1)
Hash Cond: ((“outer”.snp_id)::text = (“inner”.snp_id)::text)
-> Seq Scan on snp_allel_chr_20_kora_pt6 a (cost=0.00…78008.00 rows=2875676 width=23) (actual time=33.877…20946.906 rows=2876528 loops=1)
Filter: ((allele_1)::text <> ‘-’::text)
-> Hash (cost=481.48…481.48 rows=132 width=13) (actual time=0.367…0.367 rows=65 loops=1)
-> Bitmap Heap Scan on snps_map b (cost=2.47…481.48 rows=132 width=13) (actual time=0.162…0.297 rows=65 loops=1)
Recheck Cond: ((gene)::text = ‘C20orf19’::text)
Filter: (NOT (snp_id IS NULL))
-> Bitmap Index Scan on idx_snps_map_gene (cost=0.00…2.47 rows=135 width=0) (actual time=0.138…0.138 rows=67 loops=1)
Index Cond: ((gene)::text = ‘C20orf19’::text)
Total runtime: 22643.329 ms

Für die Where-Abfrage not allele_1=’-’ habe ich sowohl einen Index ohne als auch mit Ausdruck (not allele_1=’-’) ausprobiert, was beides keinen Unterschied gemacht hat.

Sorry habe die Abfrage vergessen :astonished:
SELECT a.allele_1, a.allele_2, a.snp_id FROM my_table a
INNER JOIN another_table b USING (snp_id)
WHERE NOT snp_id=null AND b.gene=‘SomeText’ AND NOT a.allele_1=’-’
ORDER BY b.snp_id

Deine Bedingung ’ ((allele_1)::text <> ‘-’::text)’ trifft auf recht viele Datensätze zu, sie ist nicht sehr selektiv. Das erzwingt einen Seq-Scan, exakt dort liegt Deine Zeit. Diese Bedingung trifft auf 2875676 Datensätze zu.


Ich vermute mal, Du hast eine Version vor 8.1. Stimmt das?


Andreas

Jein. Ich benutze EnterpriseDB 8.1, was wiederum auf PostgreSQL 8.1 aufbaut.

Mmh. Meiner Vermutung war, ich könnte Dir nun 8.1 mit InMemory Bitmap-Index-Scan empfehlen…
Aber das wird an dem Problem nix ändern, überleg ich mir grad.

Andreas

Ich habe probeweise die Bedingung entfertn, aber ohne wesentlich Besserung.

Konkret, explain analyse?
Hast Du mal vacuum gemacht?

Andreas

Hallo abenstex,

Du wirst mit der Abfrage keine wesentliche Performens-Verbesserung erhalten, da der INNER JOIN eine neue Tabelle erzeugt und es für die in der WHERE-Klausel enthaltenen Bedingungen es keine Index-Datei gibt. Es müssen daher die in der WHERE-Klausel enthaltenen Bedingungen in jeder Zeile der neuen Tabelle überprüft werden.

Eine Verbesserung könntest Du erhalten, wenn Du “NOT snp_id=null” durch einen LEFT- oder RIGHT-Join ersetzt. Dadurch reduziert sich die Größe der Join-Tabelle. In der WHERE-Klausel sind dann nur noch 2 Bedingungen enthalten. Zudem solltest Du “NOT a.allele_1=’-’” als erstes in der WHERE-Klausel angegeben. Zeitmäßig ist der Test eines einzelnen Zeichens ‘-’ schneller als mehrere Zeichen ‘SomeText’.





mfg

fristo

Nach einem Vacuum wurde es besser:
Sort (cost=85872.25…85874.25 rows=801 width=36) (actual time=10790.397…10797.586 rows=15340 loops=1)
Sort Key: b.snp_id
→ Hash Join (cost=501.61…85833.62 rows=801 width=36) (actual time=4534.970…10680.475 rows=15340 loops=1)
Hash Cond: ((“outer”.snp_id)::text = (“inner”.snp_id)::text)
→ Seq Scan on snp_allel_chr_20_kora_pt6 a (cost=0.00…70692.00 rows=2926400 width=23) (actual time=10.499…9052.858 rows=2926400 loops=1)
→ Hash (cost=501.26…501.26 rows=139 width=13) (actual time=0.302…0.302 rows=65 loops=1)
→ Bitmap Heap Scan on snps_map b (cost=2.49…501.26 rows=139 width=13) (actual time=0.143…0.235 rows=65 loops=1)
Recheck Cond: ((gene)::text = ‘C20orf19’::text)
Filter: (NOT (snp_id IS NULL))
→ Bitmap Index Scan on idx_snps_map_gene (cost=0.00…2.49 rows=141 width=0) (actual time=0.122…0.122 rows=67 loops=1)
Index Cond: ((gene)::text = ‘C20orf19’::text)
Total runtime: 10809.130 ms

Okay, die Schätzungen stimmen nun auch.

→ Seq Scan on snp_allel_chr_20_kora_pt6 a (cost=0.00…70692.00 rows=2926400 width=23) (actual time=10.499…9052.858 rows=2926400 loops=1)

Total runtime: 10809.130 ms

Dieser Scan ist halt das Problem…

Andreas