From: Tom Lane <tgl@sss.pgh.pa.us>
To: gry@ll.mit.edu
Cc: pgsql-sql@postgresql.org
Subject: Re: design question: status table+log table, indexes, triggers
Date: Wed, 05 Feb 2003 14:07:01 -0500
Message-ID: <28577.1044472021@sss.pgh.pa.us> (raw)
In-Reply-To: <20030205135137.4abc14b5.gry@ll.mit.edu>
References: <20030205135137.4abc14b5.gry@ll.mit.edu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, gry@ll.mit.edu
Subject: Re: design question: status table+log table, indexes, triggers
In-Reply-To: <28577.1044472021@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox