Re: Simple Join

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.