[GENERAL] query pegs beta4

2007-12-15 Thread Louis-David Mitterrand 
This new query of mine pegs beta4, it doesn't return and CPU is at 100%:

select l.id_location,l.name,
a.city
from location l, address a, show_date x, show s, show s2
where (l.id_address = a.id_address
and x.id_location = l.id_location
and s.id_show = x.id_show
and s2.show_type = s.show_type and s2.id_show = 305)
or l.id_location = 172;

The tables are not big, at most a few hundred elements each, if that.

Maybe the query itself is flawed, I haven't tried it on other versions 
of postgres.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] query pegs beta4

2007-12-15 Thread Tom Lane 
Louis-David Mitterrand [EMAIL PROTECTED] writes:
 This new query of mine pegs beta4, it doesn't return and CPU is at 100%:
   select l.id_location,l.name,
   a.city
   from location l, address a, show_date x, show s, show s2
   where (l.id_address = a.id_address
   and x.id_location = l.id_location
   and s.id_show = x.id_show
   and s2.show_type = s.show_type and s2.id_show = 305)
   or l.id_location = 172;

 The tables are not big, at most a few hundred elements each, if that.

 Maybe the query itself is flawed,

I'd say so.  Any l row with id_location = 172 joins to the cartesian
product of all the other tables.  I doubt that's what you meant.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] query pegs beta4

2007-12-15 Thread Louis-David Mitterrand 
On Sat, Dec 15, 2007 at 12:39:30PM -0500, Tom Lane wrote:
 Louis-David Mitterrand [EMAIL PROTECTED] writes:
  This new query of mine pegs beta4, it doesn't return and CPU is at 100%:
  select l.id_location,l.name,
  a.city
  from location l, address a, show_date x, show s, show s2
  where (l.id_address = a.id_address
  and x.id_location = l.id_location
  and s.id_show = x.id_show
  and s2.show_type = s.show_type and s2.id_show = 305)
  or l.id_location = 172;
 
  The tables are not big, at most a few hundred elements each, if that.
 
  Maybe the query itself is flawed,
 
 I'd say so.  Any l row with id_location = 172 joins to the cartesian
 product of all the other tables.  I doubt that's what you meant.

Hi Tom,

No, what I really meant (and clumsily attempted here) is: either return 
the list of locations that have been already used for the same
'show_type' as the current show) OR just return the newly created 
location 172.

I just backtracked and expressed the equivalent in perl, so no problem 
here.

Thanks,

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/