Re: Very slow joins

On 25 Jul 2009, at 11:36, MS wrote:

>> can we see an explain analyze at least?
>>
>
> Hi,
> Well, it won't be necessary - I mean it looks just like the explain I
> sent in my first post.

What first post? The only thing I can find is a reference in a message
by you from yesterday, to a two-year old post that you claim is about
the same problem. Though it's possible that it is the same problem,
you don't provide any data to back that up.

The message you referred to was about a one-of-a-kind problem with
communications to the client and had nothing to do with performance on
the server; is that indeed what you're seeing? In that case you should
check your network infrastructure for problems.

Usually server performance problems are due to problems with tuning
parameters or outdated statistics. Those issues can usually be solved
easily.

Without posting an EXPLAIN ANALYSE people here can only guess what
your problem is.

> BUT I found the real cause of my problem - the "fk2" field from my
> example had not only an index, but it was also a foreign key to
> another table.
> I believe the update took so long because pgsql was checking if the
> changes don't break the referential integrity.
> When I dropped the FK constraint (and index too - just in case) the
> update took around 3 minutes which is acceptable.
> So - problem solved, postgres good. ;) But isn't there a way to make
> some bulk operations without having to drop indexes/FKs?
> Something that would work like:
>
> begin transaction + forget about RI
> make some lenghty operation (update/delete...)
> if RI is OK then commit; else rollback

That seems unlikely to be the cause. From the above it seems much more
likely that you're suffering from a bad query plan instead, but you
don't provide any details.

Disabling referential integrity is a bad thing to do, and very rarely
necessary. But we don't know what you're trying to do, except that
you're updating some records that apparently have a foreign key
reference.

It would also help to know what version of PostgreSQL this is and on
what hardware and setup you're running into this issue.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4a6af5d410132049512701!