From: Richard Huxton <dev@archonet.com>
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Cc: Pgsql-Performance (E-mail) <pgsql-performance@postgresql.org>
Subject: Re: How to interpret this explain analyse?
Date: Fri, 11 Feb 2005 10:20:02 +0000
Message-ID: <420C86D2.6050907@archonet.com> (raw)
In-Reply-To: <A3D1526C98B7C1409A687E0943EAC410605EFF@obelix.askesis.nl>
References: <A3D1526C98B7C1409A687E0943EAC410605EFF@obelix.askesis.nl>
Joost Kraaijeveld wrote:
> 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:
Indexes not necessarily useful here since you're fetching all rows in A
and presumably much of B
Sort
Hash Left Join
Seq Scan on orders a
Hash
Seq Scan on klt_alg b
I've trimmed the above from your explain output. It's sequentially
scanning "b" and using a hash to join to "a" before sorting the results.
> 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)?
The cost numbers represent "effort" rather than time. They're only
really useful in that you can compare one part of the query to another.
There are two numbers because the first shows startup, the second final
time. So - the "outer" parts of the query will have increasing startup
values since the "inner" parts will have to do their work first.
The "actual time" is measured in ms, but remember to multiply it by the
"loops" value. Oh, and actually measuring the time slows the query down too.
> 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?
The key thing to look for here is the number of rows. If PG expects say
100 rows but there are instead 10,000 then it may choose the wrong plan.
In this case the estimate is 1,100,836 and the actual is 1,104,380 -
very close.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, J.Kraaijeveld@Askesis.nl
Subject: Re: How to interpret this explain analyse?
In-Reply-To: <420C86D2.6050907@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox