Hallo,
ich habe ein kleines Problem mit der Geschwindigkeit meiner abfrage. Es sind ca. 500000 Datensätze in der Tabelle.
Könnte Ihr mir tipps geben was ich besser machen kann um die Performance zu erhöhen?
SQL:
Code:
explain analyze
SELECT r.*, q.*, md5(r.xx)
FROM rr AS r
LEFT JOIN qq AS q
ON q.xx = r.xx
LEFT JOIN aa AS a
ON a.xx = r.xx
WHERE r.xx
LIKE '%00%'
OR a.aa
LIKE '%00%'
ORDER BY r.xx ASC OFFSET 499990 LIMIT 10;
Code:
Limit (cost=119429.73..119429.73 rows=1 width=219) (actual time=9940.137..9940.178 rows=10 loops=1)
-> Merge Left Join (cost=118073.37..119429.73 rows=180000 width=219) (actual time=7527.963..9721.198 rows=500000 loops=1)
Merge Cond: ((r.xx)::text = (q.xx)::text)
-> Sort (cost=118020.71..118470.71 rows=180000 width=139) (actual time=7527.929..8299.778 rows=500000 loops=1)
Sort Key: r.xx
Sort Method: external merge Disk: 74440kB
-> Hash Left Join (cost=24008.83..77080.83 rows=180000 width=139) (actual time=991.936..3698.130 rows=500000 loops=1)
Hash Cond: ((r.xx)::text = (a.xx)::text)
Filter: (((r.xx)::text ~~ '%00%'::text) OR ((a.xx)::text ~~ '%00%'::text))
-> Seq Scan on rr r (cost=0.00..15639.00 rows=500000 width=139) (actual time=0.039..486.760 rows=500000 loops=1)
-> Hash (cost=14095.37..14095.37 rows=500037 width=35) (actual time=991.382..991.382 rows=500000 loops=1)
-> Seq Scan on aa a (cost=0.00..14095.37 rows=500037 width=35) (actual time=0.024..454.691 rows=500000 loops=1)
-> Sort (cost=52.67..54.52 rows=740 width=80) (actual time=0.018..0.018 rows=0 loops=1)
Sort Key: q.xx
Sort Method: quicksort Memory: 17kB
-> Seq Scan on qq q (cost=0.00..17.40 rows=740 width=80) (actual time=0.003..0.003 rows=0 loops=1)
Total runtime: 9960.185 ms Danke im Vorraus und Grüße
Lesezeichen