Skip site navigation (1) Skip section navigation (2)

Re: Simple Join

From: Mitch Skinner <lists(at)arctur(dot)us>
To: Kevin Brown <blargity(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Simple Join
Date: 2005-12-15 11:02:06
Message-ID: 1134644526.14248.60.camel@firebolt (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Markus SchaberDate: 2005-12-15 11:03:23
Subject: Re: [postgis-users] Is my query planner failing me, or vice versa?
Previous:From: Mark KirkwoodDate: 2005-12-15 08:15:05
Subject: Re: Simple Join

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group