Re: performance comparission postgresql/ms-sql server

Поиск
Список
Период
Сортировка
От Shridhar Daithankar
Тема Re: performance comparission postgresql/ms-sql server
Дата
Msg-id 40718146.1010506@frodo.hserus.net
обсуждение исходный текст
Ответ на performance comparission postgresql/ms-sql server  ("Heiko Kehlenbrink" <Heiko.Kehlenbrink@vermes.fh-oldenburg.de>)
Список pgsql-performance
Heiko Kehlenbrink wrote:

> hi list,
>
> i want to convince people to use postgresql instead of ms-sql server, so i
> set up a kind of comparission insert data / select data from postgresql /
> ms-sql server
>
> the table i use was pretty basic,
>
> id   bigserial
> dist  float8
> x     float8
> y     float8
> z     float8
>
> i filled the table with a function which filled x,y,z with incremental
> increasing values (1,2,3,4,5,6...) and computing from that the dist value
> for every tupel (sqrt((x*x)+(y*y)+(z*z))).
>
> this works fine for both dbms
>
> postgresql needs 13:37 min for 10.000.000 tupel,
> ms-sql     needs 1:01:27 h for 10.000.000 tupel.
>
> so far so good.
>
> i attached an index on the dist row and started to query the dbs with
> scripts which select a serial row of 100.000,200.000,500.000 tupels based
> on the dist row.
> i randomizly compute the start and the end distance and made a "select
> avg(dist) from table where dist > startdist and dist < enddist"

Some basics to check quickly.

1. vacuum analyze the table before you start selecting.
2. for slow running queries, check explain analyze output and find out who takes
maximum time.
3. Check for typecasting. You need to typecast the query correctly e.g.

select avg(dist) from table where dist >startdist::float8 and dist<enddist::float8..

This might still end up with sequential scan depending upon the plan. but if
index scan is picked up, it might be plenty fast..

Post explain analyze for the queries if things don't improve.

  HTH

  Shridhar


В списке pgsql-performance по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Re: performance comparission postgresql/ms-sql server
Следующее
От: Tom Lane
Дата:
Сообщение: Re: performance comparission postgresql/ms-sql server