Tom Lane writes:
> Rob Nagler <nagler@bivio.biz> writes:
> > q5 and q6 are too complex to discuss here,
>
> How do you expect us to get better if you don't show us the problems?
With all due respect and thanks for the massive amount of help, I have
presented the problems. q5 and q6 are a subset of the following
general problems:
* Multiple ORDER BY results in no index used.
Solution: drop multiple ORDER BY, only use first
* Vacuum locks out interactive users
Solution: don't run vacuum full and only run vacuum at night
* Low cardinality index on large table confuses planner
Solution: Drop (foreign key) index, which hurts other performance
* Grouped aggregates result in disk sort
Solution: Wait to 7.4 (may work), or write in Perl (works today)
* Extreme non-linear performance (crossing magic number in
optimizer drops performance three orders of magnitude)
Solution: Don't cross magic number, or code in Perl
The general problem is that our system generates 90% of the SQL we
need. There are many advantages to this, such as being able to add
OFFSET/LIMIT support with a few lines of code in a matter of hours.
Every time we have to custom code a query, or worse, code it in Perl,
we lose many benefits. I understand the need to optimize queries, but
my general experience with Oracle is that I don't have to do this very
often. When the 80/20 rule inverts, there's something fundamentally
wrong with the model. That's where we feel we're at. It's cost us a
tremendous amount of money to deal with these query optimizations.
The solution is not to fix the queries, but to address the root
causes. That's what my other note in this thread is about. I hope
you understand the spirit of my suggestion, and work with us to
finding an acceptable approach to the general problems.
> BTW, have you tried any of this with a 7.4beta release?
I will, but for my other projects, not this one. I'll run this data,
because it's a great test case.
We have a business decision to make: devote more time to Postgres or
go with Oracle. I spent less than a day getting the data into Oracle
and to create the benchmark. The payoff is clear, now. The risk of
7.4 is still very high, because the vacuum problem still looms and a
simple "past performance is a good indicator of future performance".
Going forward, there's no choice. We've had to limit end-user
functionality to get Postgres working as well as it does, and that's
way below where Oracle is without those same limits and without any
effort put into tuning.
Thanks again for all your support.
Rob