From: | "Joost Kraaijeveld" <J(dot)Kraaijeveld(at)Askesis(dot)nl> |
---|---|
To: | "Pgsql-Performance (E-mail)" <pgsql-performance(at)postgresql(dot)org> |
Subject: | How to interpret this explain analyse? |
Date: | 2005-02-11 09:18:45 |
Message-ID: | A3D1526C98B7C1409A687E0943EAC410605EFF@obelix.askesis.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi all,
A question on how to read and interpret the explain analyse statement (and what to do)
I have a query "SELECT A.ordernummer, B.klantnummer FROM orders A LEFT OUTER JOIN klt_alg B ON A.Klantnummer=B.Klantnummer ORDER BY A.klantnummer;"
Both tables have an btree index on klantnummer (int4, the column the join is on). I have vacuumed and analyzed both tables. The explain analyse is:
QUERY PLAN
Sort (cost=220539.32..223291.41 rows=1100836 width=12) (actual time=51834.128..56065.126 rows=1104380 loops=1)
Sort Key: a.klantnummer
-> Hash Left Join (cost=41557.43..110069.51 rows=1100836 width=12) (actual time=21263.858..42845.158 rows=1104380 loops=1)
Hash Cond: (""outer"".klantnummer = ""inner"".klantnummer)
-> Seq Scan on orders a (cost=0.00..46495.36 rows=1100836 width=8) (actual time=5.986..7378.488 rows=1104380 loops=1)
-> Hash (cost=40635.14..40635.14 rows=368914 width=4) (actual time=21256.683..21256.683 rows=0 loops=1)
-> Seq Scan on klt_alg b (cost=0.00..40635.14 rows=368914 width=4) (actual time=8.880..18910.120 rows=368914 loops=1)
Total runtime: 61478.077 ms
Questions:
-> Hash Left Join (cost=41557.43..110069.51 rows=1100836 width=12) (actual time=21263.858..42845.158 rows=1104380 loops=1)
0. What exactly are the numbers in "cost=41557.43..110069.51" ( I assume for the other questions that 41557.43 is the estimated MS the query will take, what are the others)?
1. I assume that (cost=41557.43..110069.51 rows=1100836 width=12) is the estimated cost and (actual time=21263.858..42845.158 rows=1104380 loops=1) the actual cost. Is the difference acceptable?
2. If not, what can I do about it?
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J(dot)Kraaijeveld(at)Askesis(dot)nl
web: www.askesis.nl
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2005-02-11 10:20:02 | Re: How to interpret this explain analyse? |
Previous Message | Jaime Casanova | 2005-02-11 07:22:39 | Re: Benchmark |