Re: subqueries

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