Hey all,
I've been developing an electronic inventory system for a used
clothing store using Postgres and PHP3, with Netscape as my
graphical interface.
And, as the database has grown, my queries (as one would logically
expect) have been getting slower. In particular, I have a set of
queries that allow the store employees to analyze the current
inventory and the sales inventory.
Some of these queries are required to do joins on several tables.
And, they are also trying to do some simple calculations
(table.price - table.cost).
As I attempt to "fix" the slowness of the queries, I have come
to the following cross-road:
1. If I re-organize the data, I would be able to perform my queries
without executing joins on multiple tables per query.
2. As I re-organize the data, the database becomes less and less
intuitive and (seemingly) less "normal".
So, I guess my question is: how costly are joins? I've heard that
Postgres pretty much "pukes" (in terms of speed) when you're trying
to do anything more than 6 table joins in one query. This leads
me to believe that joins are fairly costly... ????
Does anyone have any words of advice for me as I battle this?
PS...
I reallize that if I do the calculations with my scripting language
and simply fetch data with my queries, I will cut down on the cost
of the queries a little bit.
Thanks,
Bob