From: Tobias Brox <tobias@nordicbet.com>
To: pgsql-performance@postgresql.org
Subject: Merge Join vs Nested Loop
Date: Tue, 26 Sep 2006 21:35:53 +0200
Message-ID: <20060926193553.GA27268@oppetid.no> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tobias@nordicbet.com
Subject: Re: Merge Join vs Nested Loop
In-Reply-To: <20060926193553.GA27268@oppetid.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox