pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: mark.lubratt@indeq.com
To: pgsql-performance@postgresql.org
Subject: Correcting Hash Join Estimates
Date: Sun,  3 Apr 2005 22:08:03 -0700
Message-ID: <20050404050803.7175.qmail@webmail10.mesa1.secureserver.net> (raw)

Hello!

I posted a similar question to this one about a month ago; but, for some
reason, it never seemed to be broadcast eventhough it ended up in the
archives.  So, since I'm still struggling with this, I thought I'd
repost...

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. There is a corresponding mis-estimation of the
amount of time taken for these steps. The database is vacuum analyzed
nightly by a cron job.  How would I go about tightening up these
errors?  I suspect that I need to SET STATISTIC on some columns, but
how can I tell which columns?

Any help would be appreciated.

WinXP (dual Xeon 1.2GB RAM) PgSQL 8.0.1
Explain Analyze:  <http://www.indeq.com/EA.txt;
View Definition: <http://www.indeq.com/VGAUA.txt;

The largest table contains about 10,000 rows.  All tables have indexes
on their foreign keys.

Thanks!
Mark





view thread (3+ messages)  latest in thread

Message-ID: <20050404050803.7175.qmail@webmail10.mesa1.secureserver.net>
Permalink:  ../20050404050803.7175.qmail@webmail10.mesa1.secureserver.net/
Also on:    postgresql.org/message-id/20050404050803.7175.qmail@webmail10.mesa1.secureserver.net

 · 

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: mark.lubratt@indeq.com
  Subject: Re: Correcting Hash Join Estimates
  In-Reply-To: <20050404050803.7175.qmail@webmail10.mesa1.secureserver.net>

* 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