pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rosser Schwarz <rschwarz@totalcardinc.com>
To: 'Gary Doades' <gpd@gpdnet.co.uk>
To: pgsql-performance@postgresql.org
Subject: Re: planner/optimizer question
Date: Thu, 29 Apr 2004 15:12:29 -0500
Message-ID: <003101c42e26$4c384e90$2500fa0a@CardServices.TCI.com> (raw)
In-Reply-To: <409153EF.24647.144E9BF7@localhost>

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.




view thread (37+ messages)  latest in thread

Message-ID: <003101c42e26$4c384e90$2500fa0a@CardServices.TCI.com>
Permalink:  ../003101c42e26$4c384e90$2500fa0a@CardServices.TCI.com/
Also on:    postgresql.org/message-id/003101c42e26$4c384e90$2500fa0a@CardServices.TCI.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: rschwarz@totalcardinc.com, gpd@gpdnet.co.uk
  Subject: Re: planner/optimizer question
  In-Reply-To: <003101c42e26$4c384e90$2500fa0a@CardServices.TCI.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox