From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org, Alexandre Leclerc <alexandre.leclerc@gmail.com>
Subject: Re: Question on pgsql optimisation of SQL and structure (index, etc)
Date: Mon, 15 Nov 2004 13:03:37 -0800
Message-ID: <200411151303.37551.josh@agliodbs.com> (raw)
In-Reply-To: <1dc7f0e3041115094846c08c34@mail.gmail.com>
References: <1dc7f0e3041115094846c08c34@mail.gmail.com>
Alexandre,
> -- Assuming those tables (not original, but enought to get the point):
>
> CREATE TABLE prod.jobs (
> job_id serial PRIMARY KEY,
> order_id integer NOT NULL REFERENCES sales.orders,
> );
>
> CREATE TABLE design.products (
> product_id serial PRIMARY KEY,
> company_id integer NOT NULL REFERENCES sales.companies ON
> UPDATE CASCADE,
> product_code varchar(24) NOT NULL,
> CONSTRAINT product_code_already_used_for_this_company UNIQUE
> (company_id, product_code)
> );
>
> CREATE TABLE prod.jobs_products (
> product_id integer REFERENCES design.products ON UPDATE CASCADE,
> ) INHERITS (prod.jobs);
First off, let me say that I find this schema rather bizarre. The standard
way to handle your situation would be to add a join table instead of
inheritance for jobs_products:
CREATE TABLE jobs_products (
job_id INT NOT NULL REFERENCES prod.jobs(job_id) ON DELETE CASCADE,
product_id INT NOT NULL REFERENCES design.products(product_id) ON UPDATE
CASCADE,
CONSTRAINT jobs_products_pk PRIMARY KEY (job_id, product_id)
);
Then this view:
> CREATE VIEW prod.orders_jobs_view AS
> SELECT job_id, order_id, product_code
> FROM (
> SELECT *, NULL AS product_id FROM ONLY prod.jobs
> UNION
> SELECT * FROM prod.jobs_products
> ) AS alljobs LEFT JOIN design.products ON alljobs.product_id =
> products.product_id;
Becomes much simpler, and better performance:
CREATE VIEW prod.orders_jobs_view AS
SELECT job_id, order_id, product_code
FROM prod.jobs LEFT JOIN prod.jobs_products ON prod.jobs.job_id =
prod.jobs_products.job_id
LEFT JOIN design.products ON prod.jobs_products.product_id =
design.products.product_id;
> I imagine that somewhere down the road, this will get slow since there
> is no index on the order_id. I tought of creating two indexes... With
> the previous VIEW and database schema, will the following boost the
> DB; as I don't know how PostgreSQL works internally:
Yes. Any time you have a foreign key, you should index it unless you have a
really good reason not to.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, alexandre.leclerc@gmail.com
Subject: Re: Question on pgsql optimisation of SQL and structure (index, etc)
In-Reply-To: <200411151303.37551.josh@agliodbs.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