4 typo
source | link

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 beterbetter choice.

CREATE INDEX tmp_as
  ON bss.amplifier_saturation (lddate DESC NULLS LAST, start);

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);

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 better choice.

CREATE INDEX tmp_as ON bss.amplifier_saturation (lddate DESC NULLS LAST, start);
3 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link

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 | FIRSTNULLS 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.

  1. Your table columns are timestamptz, but your query predicates use timestamp literals. Postgres derives the time zone from your current timezone setting and adjusts accordingly. This may or may not be as intended. It certainly makes the query volatile - depending on a setting of your session. It would be problematic for a call that could be made from different time zones (with differing sessions settings). Then you'd rather use an explicit offset or the AT TIME ZONE construct to make it stable. Details:

  2. You typically would want to exclude the upper bound for correctness. < instead of <=.

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.

  1. Your table columns are timestamptz, but your query predicates use timestamp literals. Postgres derives the time zone from your current timezone setting and adjusts accordingly. This may or may not be as intended. It certainly makes the query volatile - depending on a setting of your session. It would be problematic for a call that could be made from different time zones (with differing sessions settings). Then you'd rather use an explicit offset or the AT TIME ZONE construct to make it stable. Details:

  2. You typically would want to exclude the upper bound for correctness. < instead of <=.

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.

  1. Your table columns are timestamptz, but your query predicates use timestamp literals. Postgres derives the time zone from your current timezone setting and adjusts accordingly. This may or may not be as intended. It certainly makes the query volatile - depending on a setting of your session. It would be problematic for a call that could be made from different time zones (with differing sessions settings). Then you'd rather use an explicit offset or the AT TIME ZONE construct to make it stable. Details:

  2. You typically would want to exclude the upper bound for correctness. < instead of <=.

2 clarify, format
source | link

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.

1
source | link