X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 86600475843; Tue, 18 Feb 2003 12:38:16 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id CEE6947580B; Tue, 18 Feb 2003 12:38:15 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2845476; Tue, 18 Feb 2003 09:38:27 -0800 Content-Type: text/plain; charset="us-ascii" From: Josh Berkus Organization: Aglio Database Solutions To: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org Subject: Re: Questions about indexes? Date: Tue, 18 Feb 2003 09:37:20 -0800 User-Agent: KMail/1.4.3 Cc: rbradetich@uswest.net MIME-Version: 1.0 Message-Id: <200302180937.21001.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/116 X-Sequence-Number: 1172 Ryan, I am crossing this discussion to the PGSQL-PERFORMANCE list, which is the= =20 proper place for it. Anyone interested, please follow us there! >>>Ryan Bradetich said: > the table would look like: > 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user x has an invalid shell. > 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user y has an invalid shell. > 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user y has expired password. > 2 | Mon Feb 17 00:34:24 MST 2003 | f101 | file /foo has improper owner. > etc... >=20 > So I do not need the anomaly to be part of the index, I only need it to= =20 >=20 > I agree with you, that I would not normally add the anomally to the > index, except for the unique row requirement. Thinking about it now, > maybe I should guarentee unique rows via a check constraint... >=20 > Thanks for making me think about this in a different way! First off, I'm not clear on why a duplicate anominaly would be necessarily= =20 invalid, given the above. Not useful, certainly, but legitimate real data. I realize that you have performance considerations to work with. However, = I=20 must point out that your database design is not *at all* normalized ... and= =20 that normalizing it might solve some of your indexing problems. A normalized structure would look something like: TABLE operations=20 id serial not null primary key, host_id int not null, timeoccurred timestamp not null default now(), category varchar(5) not null, constraint operations_unq unique (host_id, timeoccurred, category) TABLE anominalies id serial not null primary key, operation_id int not null references operations(id) on delete cascade, anominaly text This structure would have two advantages for you: 1) the operations table would be *much* smaller than what you have now, as = you=20 would not be repeating rows for each anominaly.=20 2) In neither table would you be including the anominaly text in an index .= ..=20 thus reducing index size tremendously. As a warning, though: you may find that for insert speed the referential= =20 integrity key is better maintained at the middleware layer. We've had som= e=20 complaints about slow FK enforcement in Postgres. --=20 Josh Berkus Aglio Database Solutions San Francisco