Re[2]: Postgres is too slow? - Mailing list pgsql-general

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@mail.ru