From: Josh Berkus <josh@agliodbs.com>
To: Daniel Ceregatti <vi@sh.nu>
To: pgsql-performance@postgresql.org
Subject: Re: What is the best way to do attribute/values?
Date: Tue, 24 Aug 2004 13:30:32 -0700
Message-ID: <200408241330.32484.josh@agliodbs.com> (raw)
In-Reply-To: <4124EB58.9040102@sh.nu>
References: <4124EB58.9040102@sh.nu>
Folks,
> I've discussed these attempts with people in #postgresql on
> irc.freenode.net. Agliodbs (I presume you know who this is) was very
> helpful, but in end was at a loss. I find myself in the same postition
> at this time. He suggested I contact this list.
There's a couple of issues here to attack:
1) PostgreSQL is not using the most optimal plan. First, it's ignoring the
fact that all referenced columns are indexed and only using the first column,
then filtering based on the other criteria. Second, testing has shown that
a hash join would actually be faster. We've tried upping the statistics,
but it doesn't seem to have an effect on the planner's erroneous estimates.
2) Even were it using the most optimal plan, it's still to slow. As you can
see from the plan, each merge join takes about 1.5 to 2 seconds. (hash
joins are only about 0.5 seconds slower). Mysteriously, a big chunk of this
time is spent *in bewtween* planner steps, as if there was some hold-up in
retrieving the index or table pages. There may be, but Daniel and I have
not been able to diagnose the cause. It's particularly mysterious since a
filter-and-sort on a *single* criteria set, without join, takes < 400ms.
Things we've already tried to avoid going over old ground:
1) increasing statistics;
2) increasing sort_mem (to 256MB, which is overkill)
3) testing on 8.0 beta, which does not affect the issue.
At this point I'm looking for ideas. Suggestions, anyone?
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, vi@sh.nu
Subject: Re: What is the best way to do attribute/values?
In-Reply-To: <200408241330.32484.josh@agliodbs.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