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
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 |