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=c9mjYXxrUAbk6kRwm2ZPmn5ZFWXXy4ADiS7ZzovXO8PQl5VxtUJDSSMdLBAMsvqixgAF1EuQDc9DgnxMKK9yrJWRrlgK+k3mtKQlcz6wdDKKpv5LnGUSZ2WA1+5xzqCQH+YFINggRhYa/AOHwRsUB5KxFJzBcMb2qTWGbOYZK8w=
From: Kevin Brown <blargity@gmail.com>
To: Mitchell Skinner <mitch@arctur.us>
Subject: Re: Simple Join
Date: Thu, 15 Dec 2005 01:48:15 -0600
User-Agent: KMail/1.8
References: <200512141603.52583.blargity@gmail.com>
	<7484.1134600423@sss.pgh.pa.us>
	<1134629567.14248.27.camel@firebolt>
In-Reply-To: <1134629567.14248.27.camel@firebolt>
Cc: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200512150148.16125.blargity@gmail.com>

On Thursday 15 December 2005 00:52, you wrote:
> On Wed, 2005-12-14 at 17:47 -0500, Tom Lane wrote:
> > That plan looks perfectly fine to me.  You could try forcing some other
> > choices by fooling with the planner enable switches (eg set
> > enable_seqscan = off) but I doubt you'll find much improvement.  There
> > are too many rows being pulled from ordered_products to make an index
> > nestloop a good idea.
>
> 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.
>
> If you (Kevin) try adding such an index, ideally it would get used given
> that you're only pulling out a small fraction of the rows in to_ship.
> If it doesn't get used, then I had a similar issue with 8.0.3 where an
> index that was mergejoinable (only because of the restrictions in the
> where clause) wasn't getting picked up.

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.

> Mitch
>
> Kevin Brown wrote:
> > CREATE TABLE to_ship
> > (
> >   id int8 NOT NULL DEFAULT nextval(('to_ship_seq'::text)::regclass),
> >   ordered_product_id int8 NOT NULL,
> >   bounced int4 NOT NULL DEFAULT 0,
> >   operator_id varchar(20) NOT NULL,
> >   "timestamp" timestamptz NOT NULL DEFAULT ('now'::text)::timestamp(6)
> > with
> > time zone,
> >   CONSTRAINT to_ship_pkey PRIMARY KEY (id),
> >   CONSTRAINT to_ship_ordered_product_id_fkey FOREIGN KEY
> > (ordered_product_id)
> > REFERENCES ordered_products (id) ON UPDATE RESTRICT ON DELETE RESTRICT
> > )
> > WITHOUT OIDS;
> >
> > CREATE TABLE ordered_products
> > (
> >   id int8 NOT NULL DEFAULT
> > nextval(('ordered_products_seq'::text)::regclass),
> >   order_id int8 NOT NULL,
> >   product_id int8 NOT NULL,
> >   recipient_address_id int8 NOT NULL,
> >   hide bool NOT NULL DEFAULT false,
> >   renewal bool NOT NULL DEFAULT false,
> >   "timestamp" timestamptz NOT NULL DEFAULT ('now'::text)::timestamp(6)
> > with
> > time zone,
> >   operator_id varchar(20) NOT NULL,
> >   suspended_sub bool NOT NULL DEFAULT false,
> >   quantity int4 NOT NULL DEFAULT 1,
> >   price_paid numeric NOT NULL,
> >   tax_paid numeric NOT NULL DEFAULT 0,
> >   shipping_paid numeric NOT NULL DEFAULT 0,
> >   remaining_issue_obligation int4 NOT NULL DEFAULT 0,
> >   parent_product_id int8,
> >   delivery_method_id int8 NOT NULL,
> >   paid bool NOT NULL DEFAULT false,
> >   CONSTRAINT ordered_products_pkey PRIMARY KEY (id),
> >   CONSTRAINT ordered_products_order_id_fkey FOREIGN KEY (order_id)
> > REFERENCES
> > orders (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
> >   CONSTRAINT ordered_products_parent_product_id_fkey FOREIGN KEY
> > (parent_product_id) REFERENCES ordered_products (id) ON UPDATE
> > RESTRICT ON
> > DELETE RESTRICT,
> >   CONSTRAINT ordered_products_recipient_address_id_fkey FOREIGN KEY
> > (recipient_address_id) REFERENCES addresses (id) ON UPDATE RESTRICT ON
> > DELETE
> > RESTRICT
> > )
> > WITHOUT OIDS;
> >
> > === The two indexes that should matter ===
> > CREATE INDEX ordered_product_id_index
> >   ON to_ship
> >   USING btree
> >   (ordered_product_id);
> >
> > CREATE INDEX paid_index
> >   ON ordered_products
> >   USING btree
> >   (paid);
> >
> > ordered_products.id is a primary key, so it should have an implicit
> > index.