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

Paul,

Questions:
a) which linux kernel version is it?
b) what kind of a disk is it? IDE or SCSI?
c) what raw speed do you from the disk? do
'time dd if=/dev/hdxxx of=/dev/null bs=64k count=10000'

On Fri, 22 Jun 2001, Paul Mamin wrote:

> 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 :(((
>
>