Re: Re[2]: Postgres is too slow?

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