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 1B15F32B42D for ; Thu, 14 Oct 2004 20:28:17 +0100 (BST) 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 28139-03 for ; Thu, 14 Oct 2004 19:28:02 +0000 (GMT) Received: from hotmail.com (bay9-dav25.bay9.hotmail.com [64.4.46.82]) by svr1.postgresql.org (Postfix) with ESMTP id 6837A32B3F1 for ; Thu, 14 Oct 2004 20:28:02 +0100 (BST) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 14 Oct 2004 12:28:01 -0700 Received: from 67.81.98.198 by bay9-dav25.bay9.hotmail.com with DAV; Thu, 14 Oct 2004 19:27:36 +0000 X-Originating-IP: [67.81.98.198] X-Originating-Email: [awerman@hotmail.com] X-Sender: awerman@hotmail.com From: "Aaron Werman" To: "Gregory S. Williamson" , "Igor Maciel Macaubas" , References: <71E37EF6B7DCC1499CEA0316A256832801D4B992@loki.wc.globexplorer.net> Subject: Re: Performance vs Schemas Date: Thu, 14 Oct 2004 15:27:51 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Message-ID: X-OriginalArrivalTime: 14 Oct 2004 19:28:01.0699 (UTC) FILETIME=[EB72DF30:01C4B223] 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: 200410/289 X-Sequence-Number: 8765 Right - if you split a table to a lot of more selective tables, it can often dramatically change the plan options (e.g. - in a single table, selectivity for a query may be 1% and require an expensive nested loop while in the more restrictive table it may match 14% of the data and do a cheaper scan). Also - don't forget that just rebuilding a database cleanly can dramatically improve performance. The only dbms I know that indexes views is MS SQL Server 2000, where it is a limited form of materialized queries. pg doesn't do MQs, but check out functional indices. /Aaron ----- Original Message ----- From: "Gregory S. Williamson" To: "Igor Maciel Macaubas" ; Sent: Thursday, October 14, 2004 2:45 PM Subject: Re: [PERFORM] Performance vs Schemas Igor, I'm not sure if it is proper to state that schemas are themselves speeding things up. As an example, we have data that is usually accessed by county; when we put all of the data into one big table and select from it using a code for a county of interest, the process is fairly slow as there are several hundred thousand candidate rows from that county in a table with many millions of rows. When we broke out certain aspects of the data into schemas (one per county) the searches become very fast indeed because we can skip the searching for a specific county code with the relevant tables and there is less (unneeded) data in the table being searched. As always, "EXPLAIN ANALYZE ..." is your friend in understanding what the planner is doing with a given query. See for some useful information, especially under the performance tips section. HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: Igor Maciel Macaubas [mailto:igor@providerst.com.br] Sent: Thu 10/14/2004 11:38 AM To: pgsql-performance@postgresql.org Cc: Subject: [PERFORM] Performance vs Schemas Hi all, I recently migrated my database from schema 'public' to multiple schema. I have around 100 tables, and divided them in 14 different schemas, and then adapted my application to use schemas as well. I could percept that the query / insert / update times get pretty much faster then when I was using the old unique schema, and I'd just like to confirm with you if using schemas speed up the things. Is that true ? What else I can do to speed up the query processing, best pratices, recommendations ... ? What about indexed views, does postgresql supports it? Regards, Igor -- igor@providerst.com.br ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org