Re: Wrong plan or what ?

""HansH"" <hartenhans@op.het.net>
> In response to "Mendola Gaetano":
> > I'm running Postgres7.3.3 and I'm performing this simple select:
>
> Looking at your fast three step plan
> > SELECT id_class from class_default where id_provider = 39;
> > SELECT id_user from user_data where id_class in ( 48 );
> > SELECT * from user_logs where id_user in (
> >  10943,   10942,   10934,   10927,   10910,  10909 );
> I'ld stem for reordering the from and where clauses alike:
>     select *
>     from
>            class_default cd,
>            user_data ud,
>            user_logs ul
>     where
>            cd.id_provider = 39 and
>            ud.id_class = cd.id_class and
>            ul.id_user = ud.id_user;


still wrong:

Hash Join  (cost=267.10..32994.34 rows=41881 width=264) (actual
time=6620.17..6847.20 rows=94 loops=1)
   Hash Cond: ("outer".id_user = "inner".id_user)
   ->  Seq Scan on user_logs ul  (cost=0.00..25712.15 rows=1298315 width=48)
(actual time=0.01..5381.69 rows=1298351 loops=1)
   ->  Hash  (cost=266.25..266.25 rows=339 width=216) (actual
time=0.89..0.89 rows=0 loops=1)
         ->  Nested Loop  (cost=0.00..266.25 rows=339 width=216) (actual
time=0.16..0.83 rows=21 loops=1)
               ->  Seq Scan on class_default cd  (cost=0.00..1.39 rows=1
width=55) (actual time=0.08..0.09 rows=1 loops=1)
                     Filter: (id_provider = 39)
               ->  Index Scan using idx_user_data_class on user_data ud
(cost=0.00..260.00 rows=389 width=161) (actual time=0.06..0.40 rows=21
loops=1)
                     Index Cond: (ud.id_class = "outer".id_class)
 Total runtime: 6847.60 msec
(10 rows)


the returned are 94.


> Personally I dislike implied joins and rather go for _about_ this:
>     select *
>     from
>            ( class_default cd
>            LEFT JOIN user_data ud ON ud.id_class = cd.id_class )
>            LEFT JOIN user_logs ul  ON ul.id_user = ud.id_user,
>     where
>            cd.id_provider = 39;

worst:

 Merge Join  (cost=280.48..55717.14 rows=41881 width=264) (actual
time=18113.64..18182.94 rows=105 loops=1)
   Merge Cond: ("outer".id_user = "inner".id_user)
   ->  Index Scan using idx_user_user_logs on user_logs ul
(cost=0.00..51665.66 rows=1298315 width=48) (actual time=10.78..15459.37
rows=1298354 loops=1)
   ->  Sort  (cost=280.48..281.33 rows=339 width=216) (actual
time=1.11..1.20 rows=105 loops=1)
         Sort Key: ud.id_user
         ->  Nested Loop  (cost=0.00..266.25 rows=339 width=216) (actual
time=0.14..0.82 rows=21 loops=1)
               ->  Seq Scan on class_default cd  (cost=0.00..1.39 rows=1
width=55) (actual time=0.07..0.07 rows=1 loops=1)
                     Filter: (id_provider = 39)
               ->  Index Scan using idx_user_data_class on user_data ud
(cost=0.00..260.00 rows=389 width=161) (actual time=0.05..0.39 rows=21
loops=1)
                     Index Cond: (ud.id_class = "outer".id_class)
 Total runtime: 18185.61 msec

:-(



thank you anyway.

Gaetano