From: Josh Berkus <josh@agliodbs.com>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Indexing UNIONs
Date: Tue, 16 Jul 2002 15:42:23 -0700
Message-ID: <200207161542.23433.josh@agliodbs.com> (raw)
In-Reply-To: <20020716192006.GC32419@wolff.to>
References: <20020716114553.GB29323@wolff.to>
<web-1578828@davinci.ethosmedia.com>
<20020716192006.GC32419@wolff.to>
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.
>
OK, I guess I'll have to get into detail:
Table "cases" is the database's third largest table, with 100,000 records,
plus three dependant tables and 19 attributes (fields).
Table "trial groups" is a small table listing a few dozen "cases" which are
aggregated for settlement bargaining. Thus, each "trial group" relates to
one to many "cases". Beyond this relationship, trial groups has only 5
attributes and 2 dependant tables.
Table "events", the largest table in the database, contains event schedule
listing with 11 attributes and one dependant table as well as recursive
relationships between events. Each event record can be (and Must be)
related to either one Case or one Trial Group.
Thus, I need to relate (in views and queries) each Event to the Union of Cases
and Trial Groups. I just can't figure out how to do so without the database
discarding the indexes on Cases in the process and things slowing to a crawl.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, bruno@wolff.to
Subject: Re: Indexing UNIONs
In-Reply-To: <200207161542.23433.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox