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=PY1VCa0NOFJAR3Sz12yn+N+ph54TgV7rD9iPMtzMrYZIVxXay3FwMT7UhkKmzqssvfpMWZPg+eVe3OkQemLcK4v8TZu56AQaNLfc8BdrlnefCj66tm+8V71/vd8jv9uSP398YJDZCWNnwlEVMsxCPuDGW64YFOGmR/JndbOt1Rk=
From: Kevin Brown <blargity@gmail.com>
To: Jaime Casanova <systemguards@gmail.com>
Subject: Re: Simple Join
Date: Wed, 14 Dec 2005 17:44:10 -0600
User-Agent: KMail/1.8
References: <200512141603.52583.blargity@gmail.com>
	<c2d9e70e0512141523uee3ed82uc4271f8ad0928f6b@mail.gmail.com>
In-Reply-To: <c2d9e70e0512141523uee3ed82uc4271f8ad0928f6b@mail.gmail.com>
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: <200512141744.10753.blargity@gmail.com>

On Wednesday 14 December 2005 17:23, you wrote:
> what hardware?

Via 800 mhz (about equiv to a 300 mhz pentium 2)
128 mb of slow ram
4200 rpm ide hard drive.

Told you it was slow. :-)

This is not the production system.  I don't expect this to be "fast" but 
everything else happens in under 2 seconds, so I know I could do this faster.  
Especially becaue the information I'm looking for probably just needs some 
denormalization, or other such trick to pop right out.  I'm using this system 
so I can locate my performance bottlenecks easier, and actually, it's plenty 
fast enough except for this one single query.  I don't necessarily want to 
optimize the query, more than just get the info faster, so that's why I'm 
posting here.

> show the tables and the indexes for those tables

No prob:

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.