pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Brown <time@bigpond.net.au>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: bad performances using hashjoin
Date: Mon, 21 Feb 2005 13:01:20 +1000
Message-ID: <42194F00.3030704@bigpond.net.au> (raw)
In-Reply-To: <15413.1108925170@sss.pgh.pa.us>
References: <cv9vbh$1j1o$1@news.hub.org>
	<15413.1108925170@sss.pgh.pa.us>

Tom Lane wrote:

>However: the reason the second plan wins is because there are zero rows
>fetched from sat_request, and so the bulk of the plan is never executed
>at all.  I doubt the second plan would win if there were any matching
>sat_request rows.
>
That's what I thought at first, but if you look more closely, that's 
having very little impact on either the cost or actual time:

->  Index Scan using idx_id_url_sat_request on sat_request sr  (cost=0.00..3.04 rows=1 width=8) (actual time=0.031..0.031 rows=0 loops=1)


The real problem appears to be here:

->  Hash Left Join  (cost=16.14..89.16 rows=1196 width=159) (actual time=3.504..809.262 rows=1203 loops=1)


As Gaetano points out in his follow-up post, the problem still exists 
after he removed the sorts:

->  Hash Left Join  (cost=16.14..80.19 rows=1196 width=4) (actual time=7.291..13.620 rows=1203 loops=1)


The planner is not breaking up the outer join in his v_packages view:
  SELECT *
  FROM packages p LEFT OUTER JOIN package_security ps USING (id_package)

It's not being selective at all with packages, despite id_package being 
the link to sat_request.

If this is too complex for the planner, could he re-arrange his outer 
join so that's it's processed later? If he puts it in his actual query, 
for instance, will the planner flatten it out anyway?



view thread (10+ messages)  latest in thread

Message-ID: <42194F00.3030704@bigpond.net.au>
Permalink:  ../42194F00.3030704@bigpond.net.au/
Also on:    postgresql.org/message-id/42194F00.3030704@bigpond.net.au

 · 

reply

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: time@bigpond.net.au, tgl@sss.pgh.pa.us
  Subject: Re: bad performances using hashjoin
  In-Reply-To: <42194F00.3030704@bigpond.net.au>

* 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