Re[2]: Postgres is too slow?

The explanation of the SQL request that works too slow on Postgres
follows.

>> Under Postgres I filled this table by COPY FROM cause.
...
>> And ... MSSQL 7.0 worked in 2-2.5 times faster that Postgres :((

RH> Post the output of the EXPLAIN and we'll see if PG is making any odd
RH> assumptions.

THE CREATE TABLE DEFINITION (about 200 bytes per record):
----------------------------------------------------------------
CREATE TABLE CallBase (
f28 smallint NULL ,
NumberID int NULL ,
f29 varchar (18) NULL ,
f30 varchar (18) NULL ,
f10 int NULL ,
f11 smallint NULL ,
f12 smallint NULL ,
f13 smallint NULL ,
f14 smallint NULL ,
f31 datetime NULL ,
CallDuration int NULL ,
f32 int NULL ,
f33 float8 NULL ,
f34 float8 NULL ,
f35 float8 NULL ,
f36 float8 NULL ,
TarifDurationAir int NULL ,
f15 int NULL ,
f16 int NULL ,
f17 int NULL ,
f18 int NULL ,
f19 real NULL ,
f20 real NULL ,
f21 real NULL ,
f22 real NULL ,
f23 smallint NULL ,
f24 datetime NULL ,
f25 int NULL ,
f26 int NULL ,
f27 int NULL ,
f37 float8 NULL ,
int1 int NULL ,
int2 smallint NULL ,
int3 smallint NULL ,
bool1 bool NOT NULL ,
bool2 bool NOT NULL ,
bool3 bool NOT NULL ,
bool4 bool NOT NULL ,
bool5 bool NOT NULL ,
bool6 bool NOT NULL ,
bool7 bool NOT NULL ,
bool8 bool NOT NULL ,
f38 int NULL ,
f39 varchar (2) NULL ,
f40 varchar (2) NULL ,
f41 varchar (2) NULL ,
f42 int NOT NULL ,
f43 int NOT NULL ,
f44 smallint NOT NULL
);
----------------------------------------------------------------

I fill this table by COPY FROM command with 500K records, exported
from the same table from MS SQL 7.0 (SP3), and created index on field
"numberid".

postmaster runned with option -B 8000 (~64 Kb for buffers)
and the result of linux "top" command during SQL command - all is OK:
----------------------------------------------------------------
PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND
253 sql 16 0 68320 66M 66424 R 0 80.0 53.4 0:33 postmaster
----------------------------------------------------------------

the result of SELECT COUNT(*) request:
----------------------------------------------------------------
Billing=# select now(); select count(*) from callbase; select now();
now
------------------------
2001-06-21 16:52:02+06
(1 row)

count
--------
500000
(1 row)

now
------------------------
2001-06-21 16:52:44+06
(1 row)
----------------------------------------------------------------
Note: it's too too long time - 32 seconds :(

the result of SELECT SUM(x) request:
----------------------------------------------------------------
Billing=# select now(); select sum(CallDuration) from callbase; select now();
now
------------------------
2001-06-21 17:11:09+06
(1 row)

sum
----------
26249970
(1 row)

now
------------------------
2001-06-21 17:11:59+06
(1 row)
----------------------------------------------------------------
Note: it's too long time also - 50 seconds

the result of SELECT SUM(x-y) request:
----------------------------------------------------------------
Billing=# select now(); select sum(TarifDurationAir-CallDuration) from callbase; select now();
now
------------------------
2001-06-21 17:13:36+06
(1 row)

sum
----------
12318973
(1 row)

now
------------------------
2001-06-21 17:14:28+06
(1 row)
----------------------------------------------------------------
Note: it's 52 seconds

The SQL command I need to request:
----------------------------------------------------------------
select numberid, sum(TarifDurationAir-CallDuration)/count(*)
from callbase
group by numberid;
----------------------------------------------------------------

The result of EXPLAIN on this request (after this request and VACUUM
ANALYZE):
----------------------------------------------------------------
NOTICE: QUERY PLAN:

Aggregate (cost=85493.92..89243.92 rows=50000 width=12)
-> Group (cost=85493.92..86743.92 rows=500000 width=12)
-> Sort (cost=85493.92..85493.92 rows=500000 width=12)
-> Seq Scan on callbase (cost=0.00..20185.00 rows=500000 width=12)

EXPLAIN
----------------------------------------------------------------

The result of previous SQL command (with SELECT NOW() before and after
it):
----------------------------------------------------------------
now
------------------------
2001-06-21 16:59:05+06
(1 row)

numberid | ?column?
----------+---------------
56 | 19.7777777778
........................
10676 | 27.5357142857
(3562 rows)

now
------------------------
2001-06-21 17:00:58+06
(1 row)
----------------------------------------------------------------
Note: too long time - 113 seconds.

The same SQL request MS SQL 7.0 made for 24-25 seconds - that's 4.5
times slower :(((

--
Best regards,
Paul mailto:magamos(at)mail(dot)ru