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