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

Jeff Janes <jeff.janes@gmail.com> writes:

> I think the next step would be to run gdb -p <pid> (but don't start
> gdb until backend is in the middle of a slow explain), then:

Sorry, I am lack debug symbols, so call trace is incomplete:

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

#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 0x00007ff768a68376 in mergejoinscansel ()
#11 0x00007ff76896faa6 in initial_cost_mergejoin ()
#12 0x00007ff768977695 in ?? ()
#13 0x00007ff76897816c in add_paths_to_joinrel ()
#14 0x00007ff76897981b in make_join_rel ()
#15 0x00007ff768979ac9 in join_search_one_level ()
#16 0x00007ff76896a3ab in standard_join_search ()
#17 0x00007ff7689837c1 in query_planner ()
#18 0x00007ff768985260 in ?? ()
#19 0x00007ff7689870a9 in subquery_planner ()
#20 0x00007ff76898736e in standard_planner ()
#21 0x00007ff7689ef3ce in pg_plan_query ()
#22 0x00007ff7688c94a3 in ?? ()
#23 0x00007ff7688c9809 in ExplainQuery ()
#24 0x00007ff7648095e2 in ?? () from /usr/lib/postgresql/9.2/lib/pg_stat_statements.so
#25 0x00007ff7689f1f27 in ?? ()
#26 0x00007ff7689f3295 in ?? ()
#27 0x00007ff7689f388f in PortalRun ()
#28 0x00007ff7689ef96d in ?? ()
#29 0x00007ff7689f0950 in PostgresMain ()
#30 0x00007ff7689aa7a3 in ?? ()
#31 0x00007ff7689ad73c in PostmasterMain ()
#32 0x00007ff768948e4b in main ()

#0  0x00007ff766973950 in lseek64 () from /lib/libc.so.6
#1  0x00007ff7689cf88d in FileSeek ()
#2  0x00007ff7689ea09c in mdwrite ()
#3  0x00007ff7689cb12f in ?? ()
#4  0x00007ff7689cca43 in ?? ()
#5  0x00007ff7689ccf54 in ReadBufferExtended ()
#6  0x00007ff7688050ca in index_fetch_heap ()
#7  0x00007ff76880523e in index_getnext ()
#8  0x00007ff768a63306 in ?? ()
#9  0x00007ff768a67624 in ?? ()
#10 0x00007ff768a67d9c in ?? ()
#11 0x00007ff768a68376 in mergejoinscansel ()
#12 0x00007ff76896faa6 in initial_cost_mergejoin ()
#13 0x00007ff768977695 in ?? ()
#14 0x00007ff76897816c in add_paths_to_joinrel ()
#15 0x00007ff76897981b in make_join_rel ()
#16 0x00007ff768979ac9 in join_search_one_level ()
#17 0x00007ff76896a3ab in standard_join_search ()
#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 ()

#0  0x00007ff766973950 in lseek64 () from /lib/libc.so.6
#1  0x00007ff7689cf88d in FileSeek ()
#2  0x00007ff7689ea2b9 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 0x00007ff768a68376 in mergejoinscansel ()
#11 0x00007ff76896faa6 in initial_cost_mergejoin ()
#12 0x00007ff768977695 in ?? ()
#13 0x00007ff76897816c in add_paths_to_joinrel ()
#14 0x00007ff76897981b in make_join_rel ()
#15 0x00007ff768979ac9 in join_search_one_level ()
#16 0x00007ff76896a3ab in standard_join_search ()
#17 0x00007ff7689837c1 in query_planner ()
#18 0x00007ff768985260 in ?? ()
#19 0x00007ff7689870a9 in subquery_planner ()
#20 0x00007ff76898736e in standard_planner ()
#21 0x00007ff7689ef3ce in pg_plan_query ()
#22 0x00007ff7688c94a3 in ?? ()
#23 0x00007ff7688c9809 in ExplainQuery ()
#24 0x00007ff7648095e2 in ?? () from /usr/lib/postgresql/9.2/lib/pg_stat_statements.so
#25 0x00007ff7689f1f27 in ?? ()
#26 0x00007ff7689f3295 in ?? ()
#27 0x00007ff7689f388f in PortalRun ()
#28 0x00007ff7689ef96d in ?? ()
#29 0x00007ff7689f0950 in PostgresMain ()
#30 0x00007ff7689aa7a3 in ?? ()
#31 0x00007ff7689ad73c in PostmasterMain ()
#32 0x00007ff768948e4b in main ()