From: | Thomas Reiss <thomas(dot)reiss(at)dalibo(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Looks like merge join planning time is too big, 55 seconds |
Date: | 2013-08-01 10:04:15 |
Message-ID: | 51FA329F.4020609@dalibo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Le 01/08/2013 11:55, Sergey Burladyan a écrit :
> 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
Hello,
If you leave enable_mergejoin to on, what happens if you run the explain
two time in a row ? Do you get the same planning time ?
At first look, this reminds me some catalog bloat issue. Can you provide
the result of these queries :
SELECT pg_size_pretty(pg_table_size('pg_class')) AS size_pg_class;
SELECT pg_size_pretty(pg_table_size('pg_attribute')) AS size_pg_attribute;
Thanks
--
Thomas Reiss
Consultant Dalibo
http://dalibo.com - http://dalibo.org
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey Burladyan | 2013-08-01 11:27:14 | Re: Looks like merge join planning time is too big, 55 seconds |
Previous Message | Sergey Burladyan | 2013-08-01 09:55:07 | Looks like merge join planning time is too big, 55 seconds |