Skip site navigation (1) Skip section navigation (2)

Merge Join vs Nested Loop

From: Tobias Brox <tobias(at)nordicbet(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Merge Join vs Nested Loop
Date: 2006-09-26 19:35:53
Message-ID: 20060926193553.GA27268@oppetid.no (view raw or flat)
Thread:
Lists: pgsql-performance
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)

Responses

pgsql-performance by date

Next:From: Bucky JordanDate: 2006-09-26 21:25:40
Subject: Re: Decreasing BLKSZ
Previous:From: Marc MorinDate: 2006-09-26 00:29:37
Subject: Re: Decreasing BLKSZ

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group