query pegs beta4

Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.org> 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

On Sat, Dec 15, 2007 at 12:39:30PM -0500, Tom Lane wrote:

Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.org> 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,