Skip site navigation (1) Skip section navigation (2)

Re: What is the best way to do attribute/values?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Daniel Ceregatti <vi(at)sh(dot)nu>, pgsql-performance(at)postgresql(dot)org
Subject: Re: What is the best way to do attribute/values?
Date: 2004-08-24 20:30:32
Message-ID: 200408241330.32484.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2004-08-24 21:00:48
Subject: Re: What is the best way to do attribute/values?
Previous:From: Gaetano MendolaDate: 2004-08-24 16:17:29
Subject: [FUN] Performance increase?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group