Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50252=pgsql-performance-ar@postgresql.org>)
 id 1V533A-0004p6-1a
 for pgsql-performance@arkaria.postgresql.org; Fri, 02 Aug 2013 00:16:56 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50252=pgsql-performance-ar@postgresql.org>)
 id 1V5339-0000Q3-Fu
 for pgsql-performance@arkaria.postgresql.org; Fri, 02 Aug 2013 00:16:55 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <eshkinkot@gmail.com>) id 1V5337-0000OK-RU
 for pgsql-performance@postgresql.org; Fri, 02 Aug 2013 00:16:53 +0000
Received: from mail-la0-x235.google.com ([2a00:1450:4010:c03::235])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <eshkinkot@gmail.com>) id 1V5335-0005MK-BV
 for pgsql-performance@postgresql.org; Fri, 02 Aug 2013 00:16:53 +0000
Received: by mail-la0-f53.google.com with SMTP id el20so12990lab.40
 for <pgsql-performance@postgresql.org>; Thu, 01 Aug 2013 17:16:49 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=from:to:cc:subject:references:date:in-reply-to:message-id
 :user-agent:mime-version:content-type;
 bh=+qsKf06xNBWdBBOyOLe+tKdYKF3cOgn+rO7Gu7OglTI=;
 b=YwugOYoaPaEmv8PIJ5DUwvyaoOCJyaFHOy+wnwdwC0lJwbt2N8ncT603J37/0d97n8
 Nmv3TqUUmba6ObyRSDvp+LhWWKJ/RtSB+8Egnpbj0XIAOS+Vz3oHvW8VvmB+j9d7WYUR
 Aot+S94CNUKNr60suiNLhNCUJ4lfineuCjGo6bx0GAs9qhUYZnBa/3fAHkDh2T7c6Th5
 OqwpGoOcexLV921yZxCCgkxhtOculmH89eY/MHKzoGprbs5sPGc0pQW48CEZcRNI27eQ
 h/SXL5YbtqYy4u9bp92UDcf7m2uRvsO/ugboxZYCSC4D0pJOqpRzlSStI9J/07gRSA0A
 2cyw==
X-Received: by 10.152.25.135 with SMTP id c7mr1818795lag.39.1375402609747;
 Thu, 01 Aug 2013 17:16:49 -0700 (PDT)
Received: from home.progtech.ru (os.progtech.ru. [80.252.153.41])
 by mx.google.com with ESMTPSA id q6sm2154152lbv.14.2013.08.01.17.16.47
 for <multiple recipients>
 (version=TLSv1.2 cipher=RC4-SHA bits=128/128);
 Thu, 01 Aug 2013 17:16:48 -0700 (PDT)
From: Sergey Burladyan <eshkinkot@gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: "pgsql-performance\@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Looks like merge join planning time is too big, 55 seconds
References: 
 <CAJ2ymdjK-+LZHFY+cskRq98c_4F1PEfveOVNbNGpWL0Pczy5Ew@mail.gmail.com>
 <8761vpwid4.fsf@seb.koffice.internal>
 <CAMkU=1zyWZEC0YfQ0D4obWWHZqcLoLApUzf-f2+LOVH2ib=s_g@mail.gmail.com>
 <87li4lusvd.fsf@seb.koffice.internal>
 <CAMkU=1zuaFU7-DBJGawX+P+e4HjXmL+R_sxOLoB4Q18B5fRPHw@mail.gmail.com>
 <87li4l9j34.fsf@home.progtech.ru>
Date: Fri, 02 Aug 2013 04:16:46 +0400
In-Reply-To: <87li4l9j34.fsf@home.progtech.ru> (Sergey Burladyan's message of
 "Fri, 02 Aug 2013 01:50:23 +0400")
Message-ID: <87k3k5kkup.fsf@home.progtech.ru>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

I also find this trace for other query:
explain select * from xview.user_items_v v where ( v.item_id = 132358330 );

#0  0x00007ff766967620 in read () from /lib/libc.so.6
#1  0x00007ff7689cfc25 in FileRead ()
#2  0x00007ff7689ea2f6 in mdread ()
#3  0x00007ff7689cc473 in ?? ()
#4  0x00007ff7689ccf54 in ReadBufferExtended ()
#5  0x00007ff7688050ca in index_fetch_heap ()
#6  0x00007ff76880523e in index_getnext ()
#7  0x00007ff768a63306 in ?? ()
#8  0x00007ff768a67624 in ?? ()
#9  0x00007ff768a67d9c in ?? ()
#10 0x00007ff768a688fc in scalargtsel ()
#11 0x00007ff768ac5211 in OidFunctionCall4Coll ()
#12 0x00007ff768998ce5 in restriction_selectivity ()
#13 0x00007ff76896c71e in clause_selectivity ()
#14 0x00007ff76896bf60 in clauselist_selectivity ()
#15 0x00007ff76896ddfd in set_baserel_size_estimates ()
#16 0x00007ff76896abf2 in ?? ()
#17 0x00007ff76896bc97 in make_one_rel ()
#18 0x00007ff7689837c1 in query_planner ()
#19 0x00007ff768985260 in ?? ()
#20 0x00007ff7689870a9 in subquery_planner ()
#21 0x00007ff76898736e in standard_planner ()
#22 0x00007ff7689ef3ce in pg_plan_query ()
#23 0x00007ff7688c94a3 in ?? ()
#24 0x00007ff7688c9809 in ExplainQuery ()
#25 0x00007ff7648095e2 in ?? () from /usr/lib/postgresql/9.2/lib/pg_stat_statements.so
#26 0x00007ff7689f1f27 in ?? ()
#27 0x00007ff7689f3295 in ?? ()
#28 0x00007ff7689f388f in PortalRun ()
#29 0x00007ff7689ef96d in ?? ()
#30 0x00007ff7689f0950 in PostgresMain ()
#31 0x00007ff7689aa7a3 in ?? ()
#32 0x00007ff7689ad73c in PostmasterMain ()
#33 0x00007ff768948e4b in main ()

I see two code paths:
#6  0x00007ff76880523e in index_getnext ()
...
#9  0x00007ff768a67d9c in ?? ()
#10 0x00007ff768a688fc in scalargtsel ()
...

and 

#6  0x00007ff76880523e in index_getnext ()
...
#9  0x00007ff768a67d9c in ?? ()
#10 0x00007ff768a68376 in mergejoinscansel ()
...

If I not mistaken, may be two code paths like this here:
(1) mergejoinscansel -> scalarineqsel-> ineq_histogram_selectivity -> get_actual_variable_range -> index_getnext
(2) scalargtsel -> scalarineqsel -> ineq_histogram_selectivity -> get_actual_variable_range -> index_getnext

And may be get_actual_variable_range() function is too expensive for
call with my bloated table items with bloated index items_user_id_idx on it?



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance