From: Thomas Reiss <thomas.reiss@dalibo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Looks like merge join planning time is too big, 55 seconds
Date: Thu, 01 Aug 2013 12:04:15 +0200
Message-ID: <51FA329F.4020609@dalibo.com> (raw)
In-Reply-To: <CAJ2ymdjK-+LZHFY+cskRq98c_4F1PEfveOVNbNGpWL0Pczy5Ew@mail.gmail.com>
References: <CAJ2ymdjK-+LZHFY+cskRq98c_4F1PEfveOVNbNGpWL0Pczy5Ew@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: thomas.reiss@dalibo.com
Subject: Re: Looks like merge join planning time is too big, 55 seconds
In-Reply-To: <51FA329F.4020609@dalibo.com>
* 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