Alberto Dalmaso <dalmaso(at)clesius(dot)it> wrote:
> 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.
We'll need a lot more information to be able to provide useful
advice.
What version of PostgreSQL?
What OS?
What does the hardware look like? (CPUs, drives, memory, etc.)
Do you have autovacuum running? What other regular maintenance to you
do?
What does your postgresql.conf file look like? (If you can strip out
all comments and show the rest, that would be great.)
With that as background, if you can show us the schema for the
table(s) involved and the text of a query, along with the EXPLAIN
ANALYZE output (or just EXPLAIN, if the query runs too long to get the
EXPLAIN ANALYZE results) that would allow us to wee where things are
going wrong. Please show this information without setting any of the
optimizer options off; but then, as a diagnostic step, *also* show
EXPLAIN ANALYZE results when you set options to a configuration that
runs faster.
-Kevin