[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