Received: from ns2.austin.rr.com (ns1.austin.rr.com [24.93.35.63] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id f883sK769436 for ; Fri, 7 Sep 2001 23:54:20 -0400 (EDT) (envelope-from rsanford@nolimitsystems.com) Received: from mightywombat (cs2416725-252.houston.rr.com [24.167.25.252]) by ns2.austin.rr.com (8.12.0.Beta16/8.12.0.Beta16) with SMTP id f883oXjd007770 for ; Fri, 7 Sep 2001 22:50:33 -0500 From: "Robert J. Sanford, Jr." To: Subject: optimizing queries and indexes... Date: Fri, 7 Sep 2001 22:53:09 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200109/28 X-Sequence-Number: 4674 i'm fairly new at this whole database design thing and my grasp of set theory is not what it was when i was in college lo these many years past. but i want to get a better idea of how to optimize sql statements. i have several friends that are DBA's by profession and work on oracle and/or ms sql server. they have all told me that while there are some general rules to follow that each database is different. for example, one thing that one of my friends said is: select X from big_table ,little_table Generally speaking, Oracle optimizes better when the smaller/reducing tables are on the bottom and the larger tables are on the top. I believe SQLServer likes them in the opposite direction. and also: Generally speaking indexes should be built with column names in the order of higher cardinality. I frequently screw this up because it runs counter to the way you think about building your joins. An example might be as follows: An index with: Company_Id (distinct count = 4) Dept_Id (distinct count = 40) Employee_ID (distinct count = 1000+) This index should probably be in the illogical order of: Employee_Id Dept_Id Company_Id so, i am hopeful that there is some sort of postgresql performance faq for queries. also, is there a general rule as to what is considered expensive when running explain on a query? i had one query running a join and calculation across two unindexed tables. the initial plan cost about 800 with a majority of the cost being taken up in a sequential scan of about 5000 rows costing 210 and then a hash join of 225 rows across the two tables costing 585. after creating indexes on the costs dropped about in half to 106 and 299 respectively. is 800 expensive? is 400 expensive? will the cost go up when the data baloons to 100,000 rows? many thanks! rjsjr