DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:from:to:subject:date:user-agent:references:in-reply-to:cc:mime-version:content-type:content-transfer-encoding:content-disposition:message-id;
	b=GklcTlxsEdNVwJdS6Ij21EYcul/1aBoNnk863FTN7/wUlZbTBXqSXgBFEUW1DENky58CHJJ0qeBhzR0MK4MPEpGEDa1qTeG4sDeyUoZ7jzFdfwwpKH0PazJGcTjFCqCNv7sLpXY2e0/bVEMtp5ys4cfRZPg3C/fHTC+uNGsVnGY=
From: Kevin Brown <blargity@gmail.com>
To: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Simple Join
Date: Thu, 15 Dec 2005 01:46:06 -0600
User-Agent: KMail/1.8
References: <200512141603.52583.blargity@gmail.com>
	<200512141752.45866.blargity@gmail.com>
	<43A0BA70.8000802@paradise.net.nz>
In-Reply-To: <43A0BA70.8000802@paradise.net.nz>
Cc: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200512150146.06336.blargity@gmail.com>

On Wednesday 14 December 2005 18:36, you wrote:
> Well - that had no effect at all :-) You don't have and index on
> to_ship.ordered_product_id do you? - try adding one (ANALYZE again), and
> let use know what happens (you may want to play with SET
> enable_seqscan=off as well).

I _DO_ have an index on to_ship.ordered_product_id.  It's a btree.

> And also, if you are only ever interested in paid = true and
> suspended_sub = false, then you can recreate these indexes as partials -
> e.g:
>
> CREATE INDEX paid_index ON ordered_products (paid) WHERE paid = true;
> CREATE INDEX suspended_sub_index ON ordered_products (suspended_sub)
> WHERE suspended_sub = false;

They're currently defined as individuals and I'm depending on the bitmap 
indexing.

> > So what's the best way to performance wiggle this info out of the db? 
> > The list of values is only about 30 tuples long out of this query, so I
> > was figuring I could trigger on insert to to_ship to place the value into
> > another table if it didn't already exist.  I'd rather the writing be slow
> > than the reading.
>
> Yeah - all sort of horrible denormalizations are possible :-), hopefully
> we can get the original query to work ok, and avoid the need to add code
> or triggers to you app.

That'd be great.