Re: Performance issue in foreign-key-aware join estimation

From: Andreas Seltenreich <seltenreich(at)gmx(dot)de>
To: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
Subject: Re: Performance issue in foreign-key-aware join estimation
Date: 2019-07-21 12:44:31
Message-ID: 87y30r8sls.fsf@ansel.ydns.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

David Rowley writes:

> On Thu, 18 Jul 2019 at 19:24, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>> Unless there's some objection, I'll be looking into pushing both 0001
>> and 0002 in a single commit in the next few days.
>
> I've pushed this after doing a bit of final tweaking.

sqlsmith triggers an assertion in this commit (3373c7155350):

TRAP: FailedAssertion("!(rel->reloptkind == RELOPT_BASEREL)", File: "equivclass.c", Line: 764)

Here's a somewhat reduced testcase to be run on the regression db:

--8<---------------cut here---------------start------------->8---
select
max(date_mii(now()::date, 42)) over (partition by subq_1.c9 order by c3),
min(c3) over (partition by subq_1.c8 )
from
(select 1 as c3 from public.partr_def2 as ref_0
left join public.num_exp_power_10_ln as sample_0
on (ref_0.a = sample_0.id ) ) as subq_0
right join (select 1 as c8, 1 as c9) as subq_1
on (true);
--8<---------------cut here---------------end--------------->8---

Backtrace below.

regards,
Andreas

(gdb) bt
#0 __GI_raise (sig=sig(at)entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x00007face8834535 in __GI_abort () at abort.c:79
#2 0x0000562b8d2731a3 in ExceptionalCondition (
conditionName=conditionName(at)entry=0x562b8d418320 "!(rel->reloptkind == RELOPT_BASEREL)",
errorType=errorType(at)entry=0x562b8d2c601d "FailedAssertion", fileName=fileName(at)entry=0x562b8d418190 "equivclass.c",
lineNumber=lineNumber(at)entry=764) at assert.c:54
#3 0x0000562b8d067ddc in get_eclass_for_sort_expr (root=root(at)entry=0x562b8e9077c8, expr=expr(at)entry=0x7facdf610030,
nullable_relids=0x7facdf6216f8, nullable_relids(at)entry=0x7facdf615560, opfamilies=0x7facdf621348,
opcintype=opcintype(at)entry=2277, collation=collation(at)entry=0, sortref=1, rel=0x0, create_it=true) at equivclass.c:764
#4 0x0000562b8d07326e in make_pathkey_from_sortinfo (root=0x562b8e9077c8, expr=0x7facdf610030, nullable_relids=0x7facdf615560,
opfamily=397, opcintype=2277, collation=0, reverse_sort=false, nulls_first=false, sortref=1, rel=0x0, create_it=true)
at pathkeys.c:215
#5 0x0000562b8d07401f in make_pathkey_from_sortop (create_it=true, sortref=1, nulls_first=false, ordering_op=1072,
nullable_relids=0x7facdf615560, expr=0x7facdf610030, root=0x562b8e9077c8) at pathkeys.c:258
#6 make_pathkeys_for_sortclauses (root=root(at)entry=0x562b8e9077c8, sortclauses=sortclauses(at)entry=0x7facdf620f98,
tlist=tlist(at)entry=0x562b8e929768) at pathkeys.c:1086
#7 0x0000562b8d082533 in make_pathkeys_for_window (root=root(at)entry=0x562b8e9077c8, tlist=0x562b8e929768, wc=<optimized out>,
wc=<optimized out>) at planner.c:5642
#8 0x0000562b8d087c81 in create_one_window_path (wflists=<optimized out>, activeWindows=<optimized out>,
output_target=<optimized out>, input_target=<optimized out>, path=0x7facdf620ea8, window_rel=<optimized out>,
root=<optimized out>) at planner.c:4663
#9 create_window_paths (activeWindows=<optimized out>, wflists=0x7facdf613b80, output_target_parallel_safe=<optimized out>,
output_target=0x7facdf6205b8, input_target=0x7facdf6206f8, input_rel=<optimized out>, root=0x562b8e9077c8) at planner.c:4588
#10 grouping_planner (root=<optimized out>, inheritance_update=false, tuple_fraction=<optimized out>) at planner.c:2211
#11 0x0000562b8d089dfa in subquery_planner (glob=glob(at)entry=0x562b8e91bb20, parse=parse(at)entry=0x562b8e906740,
parent_root=parent_root(at)entry=0x0, hasRecursion=hasRecursion(at)entry=false, tuple_fraction=tuple_fraction(at)entry=0)
at planner.c:1013
#12 0x0000562b8d08afa7 in standard_planner (parse=0x562b8e906740, cursorOptions=256, boundParams=<optimized out>) at planner.c:406

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Korotkov 2019-07-21 12:54:03 Re: Psql patch to show access methods info
Previous Message Tomas Vondra 2019-07-21 11:34:22 Re: [PATCH] Incremental sort (was: PoC: Partial sort)