Looks like merge join planning time is too big, 55 seconds

From: Sergey Burladyan <eshkinkot(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Looks like merge join planning time is too big, 55 seconds
Date: 2013-08-01 09:55:07
Message-ID: CAJ2ymdjK-+LZHFY+cskRq98c_4F1PEfveOVNbNGpWL0Pczy5Ew@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello, i have a problem with planning time, I do not understand why this can
happen.

PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 64-bit

# explain
# select i.item_id, u.user_id from items i
# left join users u on u.user_id = i.user_id
# where item_id = 169946840;
QUERY PLAN

----------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..397.14 rows=1 width=16)
-> Index Scan using items_item_ux on items i (cost=0.00..358.84 rows=1
width=16)
Index Cond: (item_id = 169946840)
-> Index Only Scan using users_user_id_pkey on users u
(cost=0.00..38.30 rows=1 width=8)
Index Cond: (user_id = i.user_id)

time: 55919.910 ms

# set enable_mergejoin to off;

# explain
select i.item_id, u.user_id from items i
left join users u on u.user_id = i.user_id
where item_id = 169946840;
QUERY PLAN

----------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..397.14 rows=1 width=16)
-> Index Scan using items_item_ux on items i (cost=0.00..358.84 rows=1
width=16)
Index Cond: (item_id = 169946840)
-> Index Only Scan using users_user_id_pkey on users u
(cost=0.00..38.30 rows=1 width=8)
Index Cond: (user_id = i.user_id)

time: 28.874 ms

--
Sergey Burladyan

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Thomas Reiss 2013-08-01 10:04:15 Re: Looks like merge join planning time is too big, 55 seconds
Previous Message KONDO Mitsumasa 2013-08-01 02:02:06 Re: PG performance issues related to storage I/O waits