X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 79C53475A1F for ; Wed, 5 Feb 2003 14:07:03 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id C1CFB475A1E for ; Wed, 5 Feb 2003 14:07:02 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h15J715u028578; Wed, 5 Feb 2003 14:07:01 -0500 (EST) To: gry@ll.mit.edu Cc: pgsql-sql@postgresql.org Subject: Re: design question: status table+log table, indexes, triggers In-reply-to: <20030205135137.4abc14b5.gry@ll.mit.edu> References: <20030205135137.4abc14b5.gry@ll.mit.edu> Comments: In-reply-to george young message dated "Wed, 05 Feb 2003 13:51:37 -0500" Date: Wed, 05 Feb 2003 14:07:01 -0500 Message-ID: <28577.1044472021@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/102 X-Sequence-Number: 11500 george young 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