Re: BUG #19705: One NaN box makes a BRIN box_inclusion_ops index omit unrelated rows - Mailing list pgsql-bugs

From shihao zhong
Subject Re: BUG #19705: One NaN box makes a BRIN box_inclusion_ops index omit unrelated rows
Date
Msg-id CAGRkXqS=KcB0EKNpC31PnEEuP75mXOxYvYE3M80q78pZK=3TzA@mail.gmail.com
Whole thread
In response to BUG #19705: One NaN box makes a BRIN box_inclusion_ops index omit unrelated rows  (PG Bug reporting form <noreply@postgresql.org>)
Responses Re: BUG #19705: One NaN box makes a BRIN box_inclusion_ops index omit unrelated rows
List pgsql-bugs
Hi,

I can reproduce this on master, and your analysis is right. bound_box()
lets the NaN into the range summary, and then every box operator says
the range cannot match.

You suggested adding a mergeable support function, like inet has. That
works, but it needs a new pg_amproc row, so it cannot go to the back
branches. The same code is in 14 and later.

The attached 0001 fixes bound_box() instead. When an input coordinate is
NaN, the result is infinite on that side. The summary then matches any
query on that axis and the recheck does the rest. Only the NaN axis
becomes lossy, the other axis still prunes.

Existing summaries that hold a NaN stay broken until REINDEX.

0002 adds tests and is optional.

Thanks,
Shihao 


Attachment

pgsql-bugs by date:

Previous
From: shihao zhong
Date:
Subject: Re: BUG #19708: Hash Join becomes about 300x slower with higher work_mem
Next
From: PG Bug reporting form
Date:
Subject: BUG #19711: SSH tunnel with PPK identity file fails/crashes in newer pgAdmin version but works in older version