How to interpret this explain analyse? - Mailing list pgsql-performance

From Joost Kraaijeveld
Subject How to interpret this explain analyse?
Date
Msg-id A3D1526C98B7C1409A687E0943EAC410605EFF@obelix.askesis.nl
Whole thread Raw
Responses Re: How to interpret this explain analyse?
List 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.KlantnummerORDER 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=368914loops=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
estimatedMS 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.158rows=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.Kraaijeveld@Askesis.nl
web: www.askesis.nl

pgsql-performance by date:

Previous
From: Jaime Casanova
Date:
Subject: Re: Benchmark
Next
From: Richard Huxton
Date:
Subject: Re: How to interpret this explain analyse?