From: Florian Weimer <fweimer@bfk.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Abysmal hash join
Date: Mon, 11 Sep 2006 18:28:49 +0200
Message-ID: <82sliyweny.fsf@mid.bfk.de> (raw)
In-Reply-To: <3204.1157989198@sss.pgh.pa.us>
References: <821wqiy0oc.fsf@mid.bfk.de>
<22641.1157984931@sss.pgh.pa.us>
<82wt8awi21.fsf@mid.bfk.de>
<3204.1157989198@sss.pgh.pa.us>
* Tom Lane:
> Yeah, n_distinct estimation from a sample is inherently hard :-(. Given
> that you have such a long tail on the distribution, it might be worth
> your while to crank the stats target for that column all the way to the
> maximum (1000).
I've done that. Fortunately, ANALYZE time didn't increase by that
much, compared to the default (by just a factor of 10). The bitmap
scan estimate is still way off (around 8000), but let's hope that it
won't matter in practice.
> Also you need to experiment with extending the stats for the smaller
> table.
Yeah, the situation is quite similar, but on a much smaller scale.
> I believe what's happening here is that the smaller table joins only to
> less-frequent entries in the big table (correct?).
Almost. We won't select the rows based on these values, at least not
in queries of that type. The reason is simply that the result set is
too large to be useful.
> Of course, large stats targets will slow down planning to some extent,
> so you should also keep an eye on how long it takes to plan the query.
These queries are mostly ad-hoc, so a delay of a couple of seconds
doesn't matter. Only if you need to wait five minutes, it's a
different story.
It seems that the situation is under control now. Thanks.
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Durlacher Allee 47 tel: +49-721-96201-1
D-76131 Karlsruhe fax: +49-721-96201-99
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: fweimer@bfk.de, tgl@sss.pgh.pa.us
Subject: Re: Abysmal hash join
In-Reply-To: <82sliyweny.fsf@mid.bfk.de>
* 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