Обсуждение: subqueries

I have such a statement:
select * from (subquery1) as foo, (subquery2) as bar;
Both subqueries are reasonably fast (<0.5s) and generate results that have
several (<10) rows but the whole query takes forever to execute. Moreover if
I simplify those subqueries (limiting functionality) the whole select clause
works just fine. It seems that those subqueries are not executed separately.
What am I doing wrong?

Tomek

----- Original Message -----
From: "Tomasz Grobelny" <tomasz@grobelny.oswiecenia.net>
To: <pgsql-general@postgresql.org>
Sent: Sunday, June 19, 2005 6:12 PM
Subject: [GENERAL] subqueries


>I have such a statement:
> select * from (subquery1) as foo, (subquery2) as bar;
> Both subqueries are reasonably fast (<0.5s) and generate results that have
> several (<10) rows but the whole query takes forever to execute. Moreover
> if
> I simplify those subqueries (limiting functionality) the whole select
> clause
> works just fine. It seems that those subqueries are not executed
> separately.
> What am I doing wrong?

This is calling out for some EXPLAIN output (perhaps from the two subqueries
individually and then the full query).

Sean

On Monday 20 of June 2005 00:35, you wrote:
> ----- Original Message -----
> From: "Tomasz Grobelny" <tomasz@grobelny.oswiecenia.net>
> To: <pgsql-general@postgresql.org>
> Sent: Sunday, June 19, 2005 6:12 PM
> Subject: [GENERAL] subqueries
>
> >I have such a statement:
> > select * from (subquery1) as foo, (subquery2) as bar;
> > Both subqueries are reasonably fast (<0.5s) and generate results that
> > have several (<10) rows but the whole query takes forever to execute.
> > Moreover if
> > I simplify those subqueries (limiting functionality) the whole select
> > clause
> > works just fine. It seems that those subqueries are not executed
> > separately.
> > What am I doing wrong?
>
> This is calling out for some EXPLAIN output (perhaps from the two
> subqueries individually and then the full query).
>
The inner query is meant to provide all routes that go to/from selected bus
stop and outer query should provide all variations(?) of these. This worked
fine when connections table had few rows but doesn't work when it has ~3000
rows. As a solution I tried using temporary tables but it isn't that easy. I
wanted to define a function like this:
create or replace function fun(...)...
create temp table qaz as subquery1;
create temp table wsx as subquery1;
select * from qaz, wsx;
language sql;
but I get postgresql error saying that relation qaz does not exist. Well it
doesn't when the function is created but it would exist when it would be
needed. Note that that set of commands I put as function body works just fine
(just not in a function).

And here it the output you requested:

EXPLAIN SELECT connections.id_route FROM connections, stops WHERE
connections.id_stop=stops.id_stop AND stops.name='Bieżanów Nowy';
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..10.79 rows=1 width=4)
   Join Filter: ("outer".id_stop = "inner".id_stop)
   ->  Index Scan using route_element on connections  (cost=0.00..4.95 rows=1
width=8)
   ->  Index Scan using unique_name on stops  (cost=0.00..5.82 rows=1 width=4)
         Index Cond: ((name)::text = 'Bieżanów Nowy'::text)
(5 rows)
(second subquery is almost the same)

EXPLAIN SELECT foo.id_route, bar.id_route FROM
(SELECT connections.id_route FROM connections, stops WHERE
connections.id_stop=stops.id_stop AND stops.name='Bieżanów Nowy') as foo,
(SELECT connections.id_route FROM connections, stops WHERE
connections.id_stop=stops.id_stop AND stops.name='Bobrzyńskiego') as bar;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..21.58 rows=1 width=8)
   Join Filter: ("outer".id_stop = "inner".id_stop)
   ->  Nested Loop  (cost=0.00..15.75 rows=1 width=12)
         ->  Index Scan using route_element on connections  (cost=0.00..4.95
rows=1 width=8)
         ->  Nested Loop  (cost=0.00..10.79 rows=1 width=4)
               Join Filter: ("outer".id_stop = "inner".id_stop)
               ->  Index Scan using route_element on connections  
(cost=0.00..4.95 rows=1 width=8)
               ->  Index Scan using unique_name on stops  (cost=0.00..5.82
rows=1 width=4)
                     Index Cond: ((name)::text = 'Bieżanów Nowy'::text)
   ->  Index Scan using unique_name on stops  (cost=0.00..5.82 rows=1 width=4)
         Index Cond: ((name)::text = 'Bobrzyńskiego'::text)
(11 rows)

does it tell anything about why my query is so slow?

Tomek

Tomasz Grobelny <tomasz@grobelny.oswiecenia.net> writes:
> I have such a statement:
> select * from (subquery1) as foo, (subquery2) as bar;
> Both subqueries are reasonably fast (<0.5s) and generate results that have
> several (<10) rows but the whole query takes forever to execute.

The above is an unconstrained join: you are asking for the cross product
of the two subqueries.  It seems unlikely that that is what you really
want.  Perhaps you meant "subquery1 UNION ALL subquery2"?

            regards, tom lane

# tomasz@grobelny.oswiecenia.net / 2005-06-20 01:02:38 +0200:
> I wanted to define a function like this:
> create or replace function fun(...)...
> create temp table qaz as subquery1;
> create temp table wsx as subquery1;
> select * from qaz, wsx;
> language sql;
> but I get postgresql error saying that relation qaz does not exist. Well it
> doesn't when the function is created but it would exist when it would be
> needed. Note that that set of commands I put as function body works just fine
> (just not in a function).

    http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

On Monday 20 of June 2005 00:12, you wrote:
> I have such a statement:
> select * from (subquery1) as foo, (subquery2) as bar;
> Both subqueries are reasonably fast (<0.5s) and generate results that have
> several (<10) rows but the whole query takes forever to execute.
Ok, postgresql wanted to be smarter that myself. It expanded the query instead
of executing them in order given by parenthesis. The solution (or just
workaround?) was to set two variables:
from_collapse_limit = 1
join_collapse_limit = 1

Tomek