Re: planner/optimizer question

while you weren't looking, Gary Doades wrote:

> Recently I have been looking at raw performance (CPU, IO)
> rather than the plans. I have some test queries that (as far
> as I can determine) use the same access plans on PostgreSQL
> and SQLServer. Getting to the detail, an index scan of an
> index on a integer column (222512 rows) takes 60ms on
> SQLServer and 540ms on PostgreSQL.

After a recent power outage, I had the opportunity to watch both
PostgreSQL and MS SQL come back from forced shutdowns (clean,
though there were active connections, in one case a bulk insert).
PostgreSQL was available and responsive as soon as the postmaster
had started. MS SQL, on the other hand, took the better part of
an hour to become remotely usable again -- on a radically faster
machine (Dell 6650, versus the 6450 we run PostgreSQL on).

Digging a bit, I noted that once MS SQL was up again, it was
using nearly 2GB main memory even when more or less idle. From
this, and having observed the performance differences between
the two, I'm left with little alternative but to surmise that
part of MS SQL's noted performance advantage [1] is due to its
forcibly storing its indices in main memory. Its startup lag
(during which it was utterly unusable; even SELECTs blocked)
could be accounted for by reindexing the tables. [2]

Granted, this is only a hypothesis, is rather unverifyable, and
probably belongs more on ADVOCACY than it does PERFORM, but it
seemed relevant.

It's also entirely possible your indices are using inaccurate
statistical information. Have you ANALYZEd recently?

/rls

[1] Again, at least in our case, the comparison is entirely
invalid, as MS SQL gets a hell of a lot more machine than
PostgreSQL. Even so, for day-to-day work and queries, even
our DBA, an until-recently fervent MS SQL advocate can't
fault PostgreSQL's SELECT, INSERT or DELETE performance.
We still can't get UPDATEs (at least bulk such) to pass
muster.

[2] This is further supported by having observed MS SQL run a
"recovery process" on databases that were entirely unused,
even for SELECT queries, at the time of the outage. The
only thing it might conceivably need to recover on them
is in-memory indices that were lost when power was lost.

--
Rosser Schwarz
Total Card, Inc.