Subject: Re: Simple Join
From: Mitch Skinner <lists@arctur.us>
To: Kevin Brown <blargity@gmail.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <200512150148.16125.blargity@gmail.com>
References: <200512141603.52583.blargity@gmail.com>
	<7484.1134600423@sss.pgh.pa.us> <1134629567.14248.27.camel@firebolt>
	<200512150148.16125.blargity@gmail.com>
Content-Type: text/plain
Date: Thu, 15 Dec 2005 03:02:06 -0800
Message-Id: <1134644526.14248.60.camel@firebolt>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2005-12-15 at 01:48 -0600, Kevin Brown wrote:
> > Well, I'm no expert either, but if there was an index on
> > ordered_products (paid, suspended_sub, id) it should be mergejoinable
> > with the index on to_ship.ordered_product_id, right?  Given the
> > conditions on paid and suspended_sub.
> >
> The following is already there:
> 
> CREATE INDEX ordered_product_id_index
>   ON to_ship
>   USING btree
>   (ordered_product_id);
> 
> That's why I emailed this list.

I saw that; what I'm suggesting is that that you try creating a 3-column
index on ordered_products using the paid, suspended_sub, and id columns.
In that order, I think, although you could also try the reverse.  It may
or may not help, but it's worth a shot--the fact that all of those
columns are used together in the query suggests that you might do better
with a three-column index on those. 

With all three columns indexed individually, you're apparently not
getting the bitmap plan that Mark is hoping for.  I imagine this has to
do with the lack of multi-column statistics in postgres, though you
could also try raising the statistics target on the columns of interest.

Setting enable_seqscan to off, as others have suggested, is also a
worthwhile experiment, just to see what you get.

Mitch