Why does Postgres have to exclude NULL values? Because
Because NULL sorts after the defaultgreatest value in DESCENDINGASCENDING
sort order sorts NULL values firstor (being the perfectly inverted sort order ofbefore in ASCENDINGDESCENDING
) order. This does not agree withThe maximum non-null value which is returned by the aggregate function max()
, which ignores is not at the beginning / end of the index if there are NULL values. (Neither would ASC
). Adding NULLS LAST | FIRST
adjusts the sort order to the characteristic of max()
(and makes the opposite min()
more expensive). Since we are mostly interested in the latest timestamp, DESC NULLS LAST
is the beter choice.
CREATE INDEX tmp_as
ON bss.amplifier_saturation (lddate DESC DESC NULLS LAST, start);
select max(lddate)
from bss.amplifier_saturation
where start >= '1987-12-31 00:00:00'::timestamp AT TIME ZONE 'PST' -- example time zone
and start < '1988-04-09 00:00:00 PST'::timestamptz; -- shorter
PST
(pacific standard time) being a random example time zone.
Why does Postgres have to exclude NULL values? Because the default DESCENDING
sort order sorts NULL values first (being the perfectly inverted sort order of ASCENDING
). This does not agree with the aggregate function max()
, which ignores NULL values. (Neither would ASC
). Adding NULLS LAST | FIRST
adjusts the sort order to the characteristic of max()
.
CREATE INDEX tmp_as ON bss.amplifier_saturation (lddate DESC NULLS LAST, start);
select max(lddate)
from bss.amplifier_saturation
where start >= '1987-12-31 00:00:00'::timestamp AT TIME ZONE 'PST' -- example time zone
and start < '1988-04-09 00:00:00 PST'::timestamptz; -- shorter
Why does Postgres have to exclude NULL values?
Because NULL sorts after the greatest value in ASCENDING
or before in DESCENDING
order. The maximum non-null value which is returned by the aggregate function max()
is not at the beginning / end of the index if there are NULL values. Adding NULLS LAST | FIRST
adjusts the sort order to the characteristic of max()
(and makes the opposite min()
more expensive). Since we are mostly interested in the latest timestamp, DESC NULLS LAST
is the beter choice.
CREATE INDEX tmp_as
ON bss.amplifier_saturation (lddate DESC NULLS LAST, start);
select max(lddate)
from bss.amplifier_saturation
where start >= '1987-12-31 00:00:00'::timestamp AT TIME ZONE 'PST'
and start < '1988-04-09 00:00:00 PST'::timestamptz; -- shorter
PST
(pacific standard time) being a random example time zone.