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

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: (view raw or whole 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,, (select group_concat( from Logs l2 join Users u... where not in (select from Logs l3 join Users u2 where....and from Logs l

The problem lies at the "and" 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

pgsql-novice by date

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

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