[postgis-devel] EMPTY and Indexes
Paul Ramsey
pramsey at cleverelephant.ca
Mon Mar 23 08:36:55 PDT 2020
Found this little paragraph in the PgSQL docs:
"Note: In most implementations of the "not-a-number" concept, NaN is not considered equal to any other numeric value (including NaN). In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values."
Our handling of EMPTY in indexes is still inconsistent and probably really bad. I think this PgSQL approach would work much better. Basically any EMPTY could be considered over-right of any other box. That would drop all empties neatly into one leaf of the tree, while allowing the = operator to still *work* and retrieve empty values in an index scan.
Nan is kind of fun.
select 'NaN'::float8 <= 'NaN'::float8;
select 'NaN'::float8 = 0;
select 'NaN'::float8 > 100000000000000000000;
select 'NaN'::float8 > 'Inf'::float8;
More information about the postgis-devel
mailing list