pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: mark.lubratt@indeq.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Correcting Hash Join Estimates
Date: Mon, 04 Apr 2005 01:54:17 -0400
Message-ID: <17973.1112594057@sss.pgh.pa.us> (raw)
In-Reply-To: <20050404050803.7175.qmail@webmail10.mesa1.secureserver.net>
References: <20050404050803.7175.qmail@webmail10.mesa1.secureserver.net>

mark.lubratt@indeq.com writes:
> I'm trying to optimize a query and the EXPLAIN ANALYZE (see link below)
> shows that some hash join row estimates are wrong by a factor of 2-3,
> and upwards of 7-8.

I doubt that improving those estimates would lead to markedly better
results.  You need to think about improving the view design instead.
What context is this view used in --- do you just do "select * from
view_get_all_user_award2", or are there conditions added to it, or
perhaps it gets joined with other things?  Do you really need the
DISTINCT constraint?  Do you really need the ORDER BY?  Can you
simplify the WHERE clause at all?

Half a second sounds pretty decent to me for a ten-way join with a WHERE
clause as unstructured as that.  If you really need it to execute in way
less time, you're probably going to have to rethink your data
representation to make the query simpler.

			regards, tom lane



view thread (3+ messages)  latest in thread

Message-ID: <17973.1112594057@sss.pgh.pa.us>
Permalink:  ../17973.1112594057@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/17973.1112594057@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, mark.lubratt@indeq.com
  Subject: Re: Correcting Hash Join Estimates
  In-Reply-To: <17973.1112594057@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