From: mark durrant <markd89@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Select performance vs. mssql
Date: Tue, 24 May 2005 08:36:36 -0700 (PDT)
Message-ID: <20050524153637.64525.qmail@web53101.mail.yahoo.com> (raw)
First, thanks for all the helpful replies. I've
listened to the suggestions and done some more digging
and have results:
I did show_plan_all in MSSQL and found that it was
doing an Index Scan. I've read someplace that if the
data you need is all in the index, then MSSQL has a
feature/hack where it does not have to go to the
table, it can do my COUNT using the index alone. I
think this explains the 1 second query performance.
I changed the query to also include the other column
which is not indexed. The results were MSSQL now used
a TableScan and was MUCH slower than PostgreSQL.
I clustered the index on MSSQL and PostgreSQL and
increased buffers to 15000 on PGSQL. I saw a
noticeable performance increase on both. On the more
complicated query, PostgreSQL is now 3.5 seconds.
MSSQL is faster again doing an index scan and is at 2
seconds. Remember the MSSQL machine has a slower CPU
as well.
My interpretations:
--Given having to do a table scan, PostgreSQL seems to
be faster. The hardware on my PostrgreSQL machine is
nicer than the MSSQL one, so perhaps they are just
about the same speed with speed determined by the
disk.
--Tuning helps. Clustered index cut my query time
down. More buffers helped.
--As Chris pointed out, how real-world is this test?
His point is valid. The database we're planning will
have a lot of rows and require a lot of summarization
(hence my attempt at a "test"), but we shouldn't be
pulling a million rows at a time.
--MSSQL's ability to hit the index only and not having
to go to the table itself results in a _big_
performance/efficiency gain. If someone who's in
development wants to pass this along, it would be a
nice addition to PostgreSQL sometime in the future.
I'd suspect that as well as making one query faster,
it would make everything else faster/more scalable as
the server load is so much less.
Thanks again,
Mark
__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new Resources site
http://smallbusiness.yahoo.com/resources/
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: markd89@yahoo.com
Subject: Re: Select performance vs. mssql
In-Reply-To: <20050524153637.64525.qmail@web53101.mail.yahoo.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