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

From: Sergey Burladyan <eshkinkot(at)gmail(dot)com>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: "pgsql-performance\(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Looks like merge join planning time is too big, 55 seconds
Date: 2013-08-02 00:16:46
Message-ID: 87k3k5kkup.fsf@home.progtech.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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?

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2013-08-02 01:22:25 Re: subselect requires offset 0 for good performance.
Previous Message Tom Lane 2013-08-01 23:44:02 Re: subselect requires offset 0 for good performance.