Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Bruno Wolff III <bruno@wolff.to>
Subject: Re: Indexing UNIONs
Date: Tue, 16 Jul 2002 15:42:23 -0700
Cc: pgsql-sql@postgresql.org
References: <20020716114553.GB29323@wolff.to>
	<web-1578828@davinci.ethosmedia.com>
	<20020716192006.GC32419@wolff.to>
In-Reply-To: <20020716192006.GC32419@wolff.to>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200207161542.23433.josh@agliodbs.com>

Bruno,

> This sounds like a design issue. This makes it seem like the events
> should be broken out into their own table and the other two tables
> should get joined with the events table when needed.
>=20

OK, I guess I'll have to get into detail:

Table "cases" is the database's third largest table, with 100,000 records,=
=20
plus three dependant tables and 19 attributes (fields).

Table "trial groups" is a small table listing a few dozen "cases" which are=
=20
aggregated for settlement bargaining.  Thus, each "trial group" relates to=
=20
one to many "cases".  Beyond this relationship, trial groups has only 5=20
attributes and 2 dependant tables.

Table "events", the largest table in the database, contains event schedule=
=20
listing with 11 attributes and one dependant table as well as recursive=20
relationships between events.   Each event record can be (and Must be)=20
related to either one Case or one Trial Group.

Thus, I need to relate (in views and queries) each Event to the Union of Ca=
ses=20
and Trial Groups.   I just can't figure out how to do so without the databa=
se=20
discarding the indexes on Cases in the process and things slowing to a craw=
l.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco