Re: Simple Join - Mailing list pgsql-performance

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.