X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1054D3A475C for ; Mon, 15 Nov 2004 17:48:48 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 87120-04 for ; Mon, 15 Nov 2004 17:48:39 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.204]) by svr1.postgresql.org (Postfix) with ESMTP id E3D263A470C for ; Mon, 15 Nov 2004 17:48:37 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 63so537294wri for ; Mon, 15 Nov 2004 09:48:36 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=l6o43WV5+GVivDItlz3YIAjRHpJDn9OXMpzkDCQsKLA5eJ84VVedWisHsHOTM1fCVxpJtBNc1K3rcRyJl4x8Ys3p0l4tGFioXis6b0sjr8cMZJ3LN7IIji9qI79vxKIN2w+LaOZGzUTSBl1bD/6TJ7wchjyo17kqYQx8daVfAOA= Received: by 10.54.19.33 with SMTP id 33mr322678wrs; Mon, 15 Nov 2004 09:48:36 -0800 (PST) Received: by 10.54.20.23 with HTTP; Mon, 15 Nov 2004 09:48:36 -0800 (PST) Message-ID: <1dc7f0e3041115094846c08c34@mail.gmail.com> Date: Mon, 15 Nov 2004 12:48:36 -0500 From: Alexandre Leclerc Reply-To: Alexandre Leclerc To: pgsql-performance@postgresql.org Subject: Question on pgsql optimisation of SQL and structure (index, etc) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/212 X-Sequence-Number: 9166 Good day, I use pgsql 7.4: I would like to know if indexes will solve my problem (I fear the system will become slow with the time). And also some questions on how pgsql optimise for speed. *Database* -- 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); -- Assuming 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; *Question 1* Assuming this request: SELECT * FROM prod.orders_jobs_view WHERE order_id = 1; 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: CREATE UNIQUE INDEX order_jobs ON prod.jobs(order_id); CREATE UNIQUE INDEX order_jobs_products ON prod.jobs_products(order_id); *Question 2* If no to question 1, what can I do to boost the database speed. I do have prety heavy views on data, and I would like to get some speed as the DB will get filled up quickly. *Question 3* When creating a wien with linked "UNION" tables as previous... when we do a SELECT with a WHERE clause, will the database act efficiently by adding the WHERE clause to the UNIONed tables in the FROM clause? Example: SELECT * FROM prod.orders_jobs_view WHERE order_id = 1; whould cause something like SELECT job_id, order_id, product_code FROM ( SELECT *, NULL AS product_id FROM ONLY prod.jobs WHERE order_id = 1 UNION SELECT * FROM prod.jobs_products WHERE order_id = 1 ) AS alljobs LEFT JOIN design.products ON alljobs.product_id = products.product_id; in order to speed the union processing? Thank you for any help on this. -- Alexandre Leclerc