From: Dawid Kuroczko <qnex42@gmail.com>
To: kynn@panix.com <kynn@panix.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing a huge_table/tiny_table join
Date: Thu, 25 May 2006 19:07:11 +0200
Message-ID: <758d5e7f0605251007k284fe658m42958e13ee7971e1@mail.gmail.com> (raw)
In-Reply-To: <200605251631.k4PGV4O28645@panix3.panix.com>
References: <200605251631.k4PGV4O28645@panix3.panix.com>
On 5/25/06, kynn@panix.com <kynn@panix.com> wrote:
> Well, they're not my statistics; they're explain's. You mean there's
> a bug in explain? I agree that it makes no sense that the costs don't
> differ as much as one would expect, but you can see right there the
> numbers of rows for the two tables. At any rate, how would one go
> about finding an explanation for these strange stats?
Well, the query planner uses statistics to deduce the best plan
possible. Explain includes this statistical data in its output.
See:
http://www.postgresql.org/docs/8.1/interactive/planner-stats.html
...for information about what it is all about.
The idea is that your statistics are probably not detailed enough
to help the planner. See ALTER TABLE SET STATISTICS to change
that.
> More bewildering still (and infuriating as hell--because it means that
> all of my work for yesterday has been wasted) is that I can no longer
> reproduce the best query plan I posted earlier, even though the tables
> have not changed at all. (Hence I can't post the explain analyze for
> the best query plan, which Josh Drake asked for.) No matter what
> value I use for LIMIT, the query planner now insists on sequentially
> scanning huge_table and ignoring the available index. (If I turn off
> enable_seqscan, I get the second worst query plan I posted yesterday.)
>
> Anyway, I take it that there is no way to bypass the optimizer and
> instruct PostgreSQL exactly how one wants the search performed?
There is no way to bypass. But there are many ways to tune it.
Hmm, there is a probability (though statistics are more probable
go) that you're using some older version of PostgreSQL, and you're
hitting same problem as I did:
http://archives.postgresql.org/pgsql-performance/2005-07/msg00345.php
Tom has provided back then a patch, which fixed it:
http://archives.postgresql.org/pgsql-performance/2005-07/msg00352.php
...but I don't remember when it made into release.
Regfa
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: qnex42@gmail.com, kynn@panix.com
Subject: Re: Optimizing a huge_table/tiny_table join
In-Reply-To: <758d5e7f0605251007k284fe658m42958e13ee7971e1@mail.gmail.com>
* 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