Re: Wrong plan or what ?

""HansH"" <hartenhans(at)op(dot)het(dot)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