Date: Tue, 26 Sep 2006 21:35:53 +0200
From: Tobias Brox <tobias@nordicbet.com>
To: pgsql-performance@postgresql.org
Subject: Merge Join vs Nested Loop
Message-ID: <20060926193553.GA27268@oppetid.no>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Organization: Group Nordicbet
User-Agent: Mutt/1.5.9i

I have some odd cases here joining two tables - the planner insists on
Merge Join, but Nested Loop is really faster - and that makes sense,
since I'm selecting just a small partition of the data available.  All
planner constants seems to be set at the default values, the only way to
get a shift towards Nested Loops seems to be to raise the constants.  I
believe our memory is big enough to hold the indices, and that the
effective_cache_size is set to a sane value (but how to verify that,
anyway?).

What causes the nested loops to be estimated so costly - or is it the
merge joins that are estimated too cheaply?  Should I raise all the
planner cost constants, or only one of them?

Here are some sample explains:


prod=> explain analyze select * from ticket join users on users_id=users.id where ticket.created>'2006-09-25 17:00';
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..67664.15 rows=10977 width=675) (actual time=0.038..202.877 rows=10627 loops=1)
   ->  Index Scan using ticket_on_created on ticket  (cost=0.00..11665.94 rows=10977 width=80) (actual time=0.014..35.571 rows=10627 loops=1)
         Index Cond: (created > '2006-09-25 17:00:00'::timestamp without time zone)
   ->  Index Scan using users_pkey on users  (cost=0.00..5.00 rows=1 width=595) (actual time=0.007..0.008 rows=1 loops=10627)
         Index Cond: ("outer".users_id = users.id)
 Total runtime: 216.612 ms
(6 rows)

prod=> explain analyze select * from ticket join users on users_id=users.id where ticket.created>'2006-09-25 16:00';
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=12844.93..68580.37 rows=11401 width=675) (actual time=106.631..1712.458 rows=11554 loops=1)
   Merge Cond: ("outer".id = "inner".users_id)
   ->  Index Scan using users_pkey on users  (cost=0.00..54107.38 rows=174508 width=595) (actual time=0.041..1215.221 rows=174599 loops=1)
   ->  Sort  (cost=12844.93..12873.43 rows=11401 width=80) (actual time=105.753..123.905 rows=11554 loops=1)
         Sort Key: ticket.users_id
         ->  Index Scan using ticket_on_created on ticket  (cost=0.00..12076.68 rows=11401 width=80) (actual time=0.074..65.297 rows=11554 loops=1)
               Index Cond: (created > '2006-09-25 16:00:00'::timestamp without time zone)
 Total runtime: 1732.452 ms
(8 rows)