Re: Compare rows - Mailing list pgsql-performance

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