X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 27AFAD1B54D for ; Wed, 8 Oct 2003 18:56:17 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 20716-07 for ; Wed, 8 Oct 2003 15:55:28 -0300 (ADT) Received: from five.zapatec.com (66-117-150-100.web.lmi.net [66.117.150.100]) by svr1.postgresql.org (Postfix) with ESMTP id 2D81CD1B4F6 for ; Wed, 8 Oct 2003 15:55:27 -0300 (ADT) Received: from rlx11.zapatec.com (rlx11.pr.zapatec.com [192.168.1.132]) by five.zapatec.com (Postfix) with ESMTP id BABB75FEC for ; Wed, 8 Oct 2003 18:55:27 +0000 (GMT) Received: (from dror@localhost) by rlx11.zapatec.com (8.12.3/8.12.3/Submit) id h98ItR3F011683 for pgsql-performance@postgresql.org; Wed, 8 Oct 2003 11:55:27 -0700 (PDT) (envelope-from dror) Date: Wed, 8 Oct 2003 11:55:27 -0700 From: Dror Matalon To: PgSQL Performance ML Subject: Re: Compare rows Message-ID: <20031008185527.GK2979@rlx11.zapatec.com> References: <3F841A28.8060504@cranel.com> <200310080901.45093.josh@agliodbs.com> <3F843AFD.3020700@cranel.com> <3F843F73.6030302@joeconway.com> <3F8459FA.1030008@cranel.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3F8459FA.1030008@cranel.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/238 X-Sequence-Number: 3986 It's still not quite clear what you're trying to do. Many people's gut reaction is that you're doing something strange with so many columns in a table. Using your example, a different approach might be to do this instead: Day | Name | Value ------+-------------+----------- Oct 1 | OS | Solaris 5.8 Oct 1 | Patch | 108528-12 Oct 3 | Patch | 108528-13 You end up with lots more rows, fewer columns, but it might be harder to query the table. On the other hand, queries should run quite fast, since it's a much more "normal" table. But without knowing more, and seeing what the other columns look like, it's hard to tell. Dror On Wed, Oct 08, 2003 at 02:39:54PM -0400, Greg Spiegelberg wrote: > Joe Conway wrote: > >Greg Spiegelberg wrote: > > > >>The reason for my initial question was this. We save changes only. > >>In other words, if system S has row T1 for day D1 and if on day D2 > >>we have another row T1 (excluding our time column) we don't want > >>to save it. > > > > > >It still isn't entirely clear to me what you are trying to do, but > >perhaps some sort of calculated checksum or hash would work to determine > >if the data has changed? > > Best example I have is this. > > You're running Solaris 5.8 with patch 108528-X and you're collecting > that data daily. Would you want option 1 or 2 below? > > Option 1 - Store it all > Day | OS | Patch > ------+-------------+----------- > Oct 1 | Solaris 5.8 | 108528-12 > Oct 2 | Solaris 5.8 | 108528-12 > Oct 3 | Solaris 5.8 | 108528-13 > Oct 4 | Solaris 5.8 | 108528-13 > Oct 5 | Solaris 5.8 | 108528-13 > and so on... > > To find what you're running: > select * from table order by day desc limit 1; > > To find when it last changed however takes a join. > > > Option 2 - Store only changes > Day | OS | Patch > ------+-------------+----------- > Oct 1 | Solaris 5.8 | 108528-12 > Oct 3 | Solaris 5.8 | 108528-13 > > To find what you're running: > select * from table order by day desc limit 1; > > To find when it last changed: > select * from table order by day desc limit 1 offset 1; > > I selected Option 2 because I'm dealing with mounds of complicated and > varying data formats and didn't want to have to write complex queries > for everything. > > Greg > > -- > Greg Spiegelberg > Sr. Product Development Engineer > Cranel, Incorporated. > Phone: 614.318.4314 > Fax: 614.431.8388 > Email: gspiegelberg@Cranel.com > Cranel. Technology. Integrity. Focus. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com