Re: performance with query

On Tue, Jun 16, 2009 at 03:37:42PM +0200, Alberto Dalmaso wrote:
> Hi everybody, I'm creating my database on postgres and after some days
> of hard work I'm arrived to obtain good performance and owfull
> performace with the same configuration.
> I have complex query that perform very well with mergejoin on and
> nestloop off.
> If I activate nestloop postgres try to use it and the query execution
> become inconclusive: after 3 hours still no answare so I kill the query.
> Tht's ok but, with this configuration, very simple and little query like
> "slect colum from table where primarykey=value bacome incredibly slow.
> The only solutionI found at the momento is to set mergejoin to off
> before doing this query.
> That is an awfull solution because with that solution I have to change
> all the software (a big, old software) in the (many) points in witch
> this kind of query are used (the same problem to set to off mergejoin
> for all the system and activate it on che connection that have to make
> the hard query).
> Do you have any suggestion to accelerate both complex and silply query?
> I've tried a lot of configuration in enabling different "Planner Method
> Configuration" but the only combination that really accelerate hard
> query is mergejoin on and nestloop off, other settings seems to be
> useless.
> Thank's in advance.

It would be helpful if you posted EXPLAIN ANALYZE results for both queries.
This will require you to run each query to completion; if that's not possible
for the 3 hour query, at least run EXPLAIN and post those results.

- Josh / eggyknap