Обсуждение: query pegs beta4

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.

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,