Skip site navigation (1) Skip section navigation (2)

Re: planner/optimizer question

From: "Rosser Schwarz" <rschwarz(at)totalcardinc(dot)com>
To: "'Gary Doades'" <gpd(at)gpdnet(dot)co(dot)uk>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: planner/optimizer question
Date: 2004-04-29 20:12:29
Message-ID: 003101c42e26$4c384e90$2500fa0a@CardServices.TCI.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.


In response to

Responses

pgsql-performance by date

Next:From: Gary DoadesDate: 2004-04-29 20:26:18
Subject: Re: planner/optimizer question
Previous:From: Gary DoadesDate: 2004-04-29 19:23:19
Subject: Re: planner/optimizer question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group