pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Mitch Skinner <lists@arctur.us>
To: Kevin Brown <blargity@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Simple Join
Date: Thu, 15 Dec 2005 03:02:06 -0800
Message-ID: <1134644526.14248.60.camel@firebolt> (raw)
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>

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




view thread (19+ messages)  latest in thread

Message-ID: <1134644526.14248.60.camel@firebolt>
Permalink:  ../1134644526.14248.60.camel@firebolt/
Also on:    postgresql.org/message-id/1134644526.14248.60.camel@firebolt

 · 

reply
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-performance@postgresql.org
  Cc: lists@arctur.us, blargity@gmail.com
  Subject: Re: Simple Join
  In-Reply-To: <1134644526.14248.60.camel@firebolt>

* 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