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: | Whole Thread | Raw Message | 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
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 |