Re: How to find greatest record before known values fast

Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> writes:
> So kellaaeg is a time? Your best bet here would be to create an index
> that is an actual timestamp comprised of both kuupaev and kellaaeg.

The real problem with this query, or at least with the index design,
is that the index design isn't accounting for the need to constrain
"laonr". The best way to create the index is with laonr first, and
instead of the max() write something like

regression=# create table foo (f1 int, f2 date, f3 bpchar(5));
CREATE TABLE
regression=# create index on foo (f1, f2, f3);
CREATE INDEX
regression=# explain select * from foo where f1 = 1 and (f2,f3) <= (current_date, '23 59') order by f2 desc, f3 desc limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Limit (cost=0.01..5.44 rows=1 width=17)
-> Index Scan Backward using foo_f1_f2_f3_idx on foo (cost=0.01..16.32 rows=3 width=17)
Index Cond: ((f1 = 1) AND (ROW(f2, f3) <= ROW(('now'::text)::date, '23 59'::bpchar)))
(3 rows)

(tested on 9.0.18, should work on newer versions too)

BTW, the fact that newer versions are refusing to create an index on
"kuupaev||kellaaeg" should not be dismissed as mere pedantry.
The reason for that is that this expression involves a cast from
date to text, and the format of the text you get depends on DateStyle.
And that should call your attention to the fact that *the original query
gives the wrong answer*, or at least an answer that I bet is not the one
you want, unless DateStyle chances to be ISO.

IMO, one of the ten deadly sins of database design is using text-string
mashing to accomplish operations that are not naturally textual. This
query and the table design itself are in dire need of sackcloth and ashes.
Had the table designer had the wit to use a timestamp field rather than
this unholy mashup, we'd not be having this conversation.

regards, tom lane