Guillaume,

> We reproduced the problem on a 7.4.5 and on a 7.4.7 server.
> * we load the dump in a new database
> * query: it's fast (< 1ms)
> * VACUUM FULL ANALYZE;
> * query: it's really slow (130ms) and it's another plan
> * set enable_seqscan=off;
> * query: it's fast (< 1ms) : it uses the best plan

Looking at this, the planner seems convinced that the merge join is the 
easiest way to do the OUTER JOINS, but it appears to be wrong; a nested loop 
is faster.

This isn't the only place I've encountered our optimizer doing this -- 
underestimating the cost of a merge join.  This seems to be becuase the 
merge_join vs. nested_loop decision seems to be being made in the planner 
without taking the double-sort and index access costs into account.   This 
query is an excellent example:

"good" plan:
 Nested Loop Left Join  (cost=2.44..17.36 rows=1 width=5532) (actual 
time=0.441..0.466 rows=1 loops=1)
   Join Filter: ("outer".parent_application_id = "inner".application_id)
   ->  Nested Loop Left Join  (cost=2.44..15.73 rows=1 width=5214) (actual 
time=0.378..0.402 rows=1 loops=1)

See, here the planner thinks that the 2 nested loops will cost "35".  

"bad" plan:
 Merge Right Join  (cost=9.27..9.48 rows=1 width=545) (actual 
time=129.364..129.365 rows=1 loops=1)
   Merge Cond: ("outer".application_id = "inner".parent_application_id)
   ->  Index Scan using applicati_applicati_id_p_ogstm on applications t116  
(cost=0.00..5.51 rows=28 width=20) (actual time=0.030..0.073 rows=28 loops=1)
   ->  Sort  (cost=9.27..9.27 rows=1 width=529) (actual time=129.202..129.203 
rows=1 loops=1)
         Sort Key: t22.parent_application_id
         ->  Merge Right Join  (cost=8.92..9.26 rows=1 width=529) (actual 
time=129.100..129.103 rows=1 loops=1)
               Merge Cond: ("outer".object_id = "inner".parent_application_id)
               ->  Index Scan using acs_objects_object_id_p_hhkb1 on 
acs_objects t98  (cost=0.00..2554.07 rows=33510 width=81) (actual 
time=0.043..56.392 rows=33510 loops=1)
               ->  Sort  (cost=8.92..8.93 rows=1 width=452) (actual 
time=0.309..0.310 rows=1 loops=1)
                     Sort Key: t22.parent_application_id

Here the planner chooses a merge right join.  This decision seems to have been 
made entirely on the basis of the cost of the join itself (total of 17) 
without taking the cost of the sort and index access (total of 2600+) into 
account.

Tom, is this a possible error in planner logic?


-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to