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
tablesare 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
frequentlyscrew 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
