Date: Thu, 1 Feb 2007 12:20:45 -0600
From: Frank Wiles <frank@wiles.org>
To: "Michael Artz" <mlartz@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Subselect query enhancement
Message-Id: <20070201122045.79ff29c8.frank@wiles.org>
In-Reply-To: <e9c163070702010842ja639378v75e8c0eb757d9b72@mail.gmail.com>
References: <e9c163070702010842ja639378v75e8c0eb757d9b72@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Thu, 1 Feb 2007 11:42:03 -0500
"Michael Artz" <mlartz@gmail.com> wrote:

> I'm needing help determining the best all-around query for the
> following situation.  I have primary table that holds ip information
> and two other tables that hold event data for the specific IP in with
> a one-to-many mapping between them, ie:
> 
> CREATE TABLE ip_info (
>     ip IP4,
>     --other data
> );
> 
> CREATE TABLE network_events (
>     ip IP4 NOT NULL REFERENCES ip_info(ip),
>     name VARCHAR,
>     port INTEGER,
>     --other data
> );
> 
> CREATE TABLE host_events (
>     ip IP4 NOT NULL REFERENCES ip_info(ip),
>     name VARCHAR
>     port INTEGER,
>     --other data
> );

It would probably help to have an index on that column for all three
tables, then I would wager using joins will be the speed winner. 

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------