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

Re: Simply join in PostrgeSQL takes too long

From: Vitaly Belman <vitalib(at)012(dot)net(dot)il>
To: Manfred Koizar <mkoi-pg(at)aon(dot)at>
Cc: Rod Taylor <pg(at)rbt(dot)ca>,Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Simply join in PostrgeSQL takes too long
Date: 2004-04-29 21:09:36
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Hello Manfred,

I thank everyone for helping me on this - So many tips.

I am in the middle of going through them all, till now disabling the
enable_mergejoin really helped.

Also, I agree that the design might be flawed (I could use triggers
and stuff like that) but for now I am just comparing how my project
will run on PostgreSQL (Considering migration from MySQL).

I'll be reporting back on how the other stuff helped.

 Vitaly Belman
 ICQ: 1912453
 AIM: VitalyB1984
 MSN: tmdagent(at)hotmail(dot)com
 Yahoo!: VitalyBe

Wednesday, April 28, 2004, 11:24:41 AM, you wrote:

MK> On Tue, 27 Apr 2004 18:01:34 -0400, Rod Taylor <pg(at)rbt(dot)ca> wrote:
>>On Tue, 2004-04-27 at 17:27, Vitaly Belman wrote:
>>> Hello pgsql-performance,
>>>   I discussed the whole subject for some time in DevShed and didn't
>>>   achieve much (as for results). I wonder if any of you guys can help
>>>   out:

>>The point is that a book cannot be of a certain genre more than once.

MK> Rod, he has a hierarchy of genres.  Genre 1 has 6379 child genres and a
MK> book can be in more than one of these.

MK> Vitaly, though LIMIT makes this look like a small query, DISTINCT
MK> requires the whole result set to be retrieved.  0.7 seconds doesn't look
MK> so bad for several thousand rows.  Did you try with other genre_ids?

MK> Maybe a merge join is not the best choice.  Set enable_mergejoin to
MK> false and see whether you get a (hopefully faster) hash join, assuming
MK> that sort_mem is large enough to keep the hash table in memory.

MK> If you send me your table contents I'll try it on Linux.

MK> Servus
MK>  Manfred

In response to


pgsql-performance by date

Next:From: Gary DoadesDate: 2004-04-29 21:15:12
Subject: Re: planner/optimizer question
Previous:From: Gary DoadesDate: 2004-04-29 21:08:28
Subject: Re: planner/optimizer question

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