Re: design question: status table+log table, indexes, triggers - Mailing list pgsql-sql

george young <gry@ll.mit.edu> writes:
> This schema seemed logical at the outset, but the most common query is:

> select m.machine_name, m.text, ml.status, ml.date 
>    from machine m, machine_log ml 
>    where m.machine_name=ml.machine_name and ml.date=(select max(date)from
> machine_log where machine_name=ml.machine_name);

You can do this a lot better with SELECT DISTINCT ON --- see the "weather
report" example in the SELECT reference page.  Given a suitable index
it should even be pretty quick.
        regards, tom lane