Re: Wrong plan or what ? - Mailing list pgsql-admin
From | Mendola Gaetano |
---|---|
Subject | Re: Wrong plan or what ? |
Date | |
Msg-id | 006b01c35844$739ec030$32add6c2@mm.eutelsat.org Whole thread Raw |
In response to | Wrong plan or what ? ("Mendola Gaetano" <mendola@bigfoot.com>) |
List | pgsql-admin |
""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
pgsql-admin by date: