Re: PostgreSQL a slow DB?

Quoth "Relaxin" <me(at)yourhouse(dot)com>:
> "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote in message
> news:200604131313(dot)k3DDD6Q05741(at)candle(dot)pha(dot)pa(dot)us(dot)(dot)(dot)
>>
>> My point was that:
>>
>> >> slowest I am thinking where there is smoke there's fire.
>>
>> is not a valid method of analysis.
>
> It works for me...

Except when it doesn't work. It's a completely non-scientific method
of determining answers, and has *enormous* bias due to ignorance.

Traditionally, MySQL(tm) has had a lot of loosely-done, unanalyzed
"benchmarketing" done where the tests done are those that can be done
without any real design. MySQL(tm) probably still has some advantages in
performance when the usage patterns combine:
a) Single user access
b) A series of simple queries expected to access one or two tuples
c) Low system load
d) A pattern that discourages transaction usage

Any "designed in 15 minutes" sort of benchmark is quite likely to fit
into this.

"Let's go insert 20000 rows into a table, and then run 20000 queries
that each pull one of those rows, and see how long it takes."

It has been common for MySQL(tm) with MyISAM to win these sorts of
"contests" that are obviously biased to what it was designed to do
well, as a thin veneering of SQL on top of a B-Tree ISAM library.

Vary any of those assumptions, and the results change pretty
substantially.

-> MyISAM doesn't scale at all well with many concurrent updaters,
whereas PostgreSQL, for instance, handles that extremely well.
-> No transaction overhead goes along with no transactional integrity;
you can't expect consistent results to come back without your
application being very careful to never cross any of the
"semantic lines" that will make MySQL(tm) eat your data.
-> There's an interesting paucity of benchmarks involving the newer
"transactional storage engines" (and presumably there never will
be any, as they are now all owned by "no, you can't publish that
benchmark" Oracle). Indications are that the InnoDB engine
drops performance by a fair bit, particularly any time there is
need to roll back transactions...
-> Complex queries favor the systems with better query optimizers,
and PostgreSQL does Quite Well there. DB2 is probably the best
available, optimization-wise, but that's not surprising, as
their developers have been at it for about 30 years now.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://linuxdatabases.info/info/slony.html
"MICROS~1: The People who Brought the Y2K Bug into Software Titling"
-- cbbrowne(at)hex(dot)net