From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Brown <time@bigpond.net.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: bad performances using hashjoin
Date: Sun, 20 Feb 2005 22:48:06 -0500
Message-ID: <20950.1108957686@sss.pgh.pa.us> (raw)
In-Reply-To: <42194F00.3030704@bigpond.net.au>
References: <cv9vbh$1j1o$1@news.hub.org>
<15413.1108925170@sss.pgh.pa.us>
<42194F00.3030704@bigpond.net.au>
David Brown <time@bigpond.net.au> writes:
> The planner is not breaking up the outer join in his v_packages view:
The planner doesn't make any attempt to rearrange join order of outer
joins. There are some cases where such a rearrangement is OK, but there
are other cases where it isn't, and we don't currently have the logic
needed to tell which is which.
In the particular case at hand here, 8.0's hack to suppress evaluating
the outer side of a hash join after finding the inner side is empty
would eliminate the complaint.
In the original message, it did seem that the packages-to-
package_security join is taking a lot longer than one would expect:
-> Hash Left Join (cost=15.54..86.42 rows=1097 width=162) (actual time=2.978..6087.608 rows=1104 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Seq Scan on packages p (cost=0.00..53.48 rows=1097 width=146) (actual time=0.011..7.978 rows=1104 loops=1)
-> Hash (cost=13.69..13.69 rows=738 width=20) (actual time=2.061..2.061 rows=0 loops=1)
-> Seq Scan on package_security ps (cost=0.00..13.69 rows=738 width=20) (actual time=0.027..1.289 rows=747 loops=1)
but this behavior isn't reproduced in the later message, so I wonder if
it wasn't an artifact of something else taking a chunk of time.
regards, tom lane
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: tgl@sss.pgh.pa.us, time@bigpond.net.au
Subject: Re: bad performances using hashjoin
In-Reply-To: <20950.1108957686@sss.pgh.pa.us>
* 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