From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: Query Performance SQL Server vs. Postgresql
Date: Wed, 17 Nov 2010 21:47:31 +0100
Message-ID: <4CE43F63.2030806@fuzzy.cz> (raw)
In-Reply-To: <AANLkTi=yFV69yNZpHzKiZRtZ0X0PtKcpOrMNNkAo+Si6@mail.gmail.com>
References: <COL115-W5250BF8BDC50E0A64DABE4A8360@phx.gbl>
<AANLkTin+MPhrYUzkCZjdAMLhF3KKZCCuvDm0=Npf9jz7@mail.gmail.com>
<COL115-W3987D9D7DE908C5C47792DA8380@phx.gbl>
<AANLkTi=yFV69yNZpHzKiZRtZ0X0PtKcpOrMNNkAo+Si6@mail.gmail.com>
Dne 17.11.2010 05:47, Pavel Stehule napsal(a):
> 2010/11/17 Humair Mohammed <humairm@hotmail.com>:
>>
>> There are no indexes on the tables either in SQL Server or Postgresql - I am
>> comparing apples to apples here. I ran ANALYZE on the postgresql tables,
Actually no, you're not comparing apples to apples. You've provided so
little information that you may be comparing apples to cucumbers or
maybe some strange animals.
1) info about the install
What OS is this running on? I guess it's Windows in both cases, right?
How nuch memory is there? What is the size of shared_buffers? The
default PostgreSQL settings is very very very limited, you have to bump
it to a much larger value.
What are the other inportant settings (e.g. the work_mem)?
2) info about the dataset
How large are the tables? I don't mean number of rows, I mean number of
blocks / occupied disk space. Run this query
SELECT relname, relpages, reltuples, pg_size_pretty(pg_table_size(oid))
FROM pg_class WHERE relname IN ('table1', 'table2');
3) info about the plan
Please, provide EXPLAIN ANALYZE output, maybe with info about buffers,
e.g. something like
EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT ...
4) no indexes ?
Why have you decided not to use any indexes? If you want a decent
performance, you will have to use indexes. Obviously there is some
overhead associated with them, but it's premature optimization unless
you prove the opposite.
BTW I'm not a MSSQL expert, but it seems like it's building a bitmap
index on the fly, to synchronize parallelized query - PostgreSQL does
not support that.
regards
Tomas
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: tv@fuzzy.cz
Subject: Re: Query Performance SQL Server vs. Postgresql
In-Reply-To: <4CE43F63.2030806@fuzzy.cz>
* 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