From: Kevin Brown <blargity@gmail.com>
To: Jaime Casanova <systemguards@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Simple Join
Date: Wed, 14 Dec 2005 17:44:10 -0600
Message-ID: <200512141744.10753.blargity@gmail.com> (raw)
In-Reply-To: <c2d9e70e0512141523uee3ed82uc4271f8ad0928f6b@mail.gmail.com>
References: <200512141603.52583.blargity@gmail.com>
<c2d9e70e0512141523uee3ed82uc4271f8ad0928f6b@mail.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.
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: blargity@gmail.com, systemguards@gmail.com
Subject: Re: Simple Join
In-Reply-To: <200512141744.10753.blargity@gmail.com>
* 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