Infinite/Huge loop in query

From: Vaduvoiu Tiberiu <vaduvoiutibi(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Infinite/Huge loop in query
Date: 2010-12-10 10:20:43
Message-ID: 794207.6040.qm@web114717.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have a huge query which for some reason, runs without ending(after 1 hour I stopped the process because obviously something was wrong). The query itself is too bulky to paste here so I will try to paste only the part that (i think) causes the problem:

select l.id,l.date, (select group_concat(u.id) from Logs l2 join Users u... where u.id not in (select u2.id from Logs l3 join Users u2 where....and l3.id=l.id)) from Logs l

The problem lies at the "and l3.id=l.id" part. Without this small comparison, the query runs in 14 seconds. So I'm pretty sure this is the problem. Now the Logs table have about 5 million records, and I'm guessing that somehow he tries to compare each of the 5 mil records with (again) each of the 5 mil recors, causing these huge periods of time. Can anybody actually tell me if the comparison makes any sense, since I find it weird to compare the id's of the same tables between themselves (both l3 and l are infact Logs table). In my oppinion the comparison will always return true so there is no reason for it. Am I right or am I missing some fundamental rules here?

Thanks in advance

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Jasen Betts 2010-12-11 09:52:29 Re: Restoring from pg_data
Previous Message Michael Wood 2010-12-09 09:18:06 Re: Understanding the behaviour of hostname in psql