Re: performance comparission postgresql/ms-sql server

Heiko,

> 100.000 from 50.000.000:
>
> postgres: 0.88 sec
> ms-sql: 0.38 sec
>
> 200.000 from 50.000.000:
>
> postgres: 1.57 sec
> ms-sql: 0.54 sec
>
> 500.000 from 50.000.000:
>
> postgres: 3.66 sec
> ms-sql: 1.18 sec

Questions:

1. Is this the time to return *all rows* or just the first row? Given the
different way that PostgreSQL fetches rows to the client from MSSQL, it makes
a difference.

2. What are your sort-mem and shared-mem settings?

3. Have you tried clustering the table?

4. Have you done a comparison of selecting random or scattered, instead of
serial rows? MSSQL has a tendency to physically store rows in "order" which
gives it a certain advantage in this kind of query.

--
Josh Berkus
Aglio Database Solutions
San Francisco