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

I was curious about this result. So I tested on my test PC and got 4.3 to 14
times
faster results than Mamin's results.

*SNIP*
>
> 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".
*SNIP*
> 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 :(

It's surely so slow. (It tooks 42 seconds, intead of 32 seconds :)
I created table from your schema and 500k records.
It took 3 seconds to finish query on my system. My test PC is not
highend PC, but old slow desktop with Celeron 466 + 192MB RAM
+ UMDA33 HDD.

Took 3 seconds. (14 times faster - I have primary key)

yohgaki=# select now(); select count(id) from callbase; select now();
          now
------------------------
 2001-06-22 16:39:53+09
(1 row)

 count
--------
 500001
(1 row)

          now
------------------------
 2001-06-22 16:39:56+09
(1 row)

yohgaki=#


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

Took 10 seconds. (5 times faster)

yohgaki=# select now(); select sum(CallDuration) from callbase; select now();
          now
------------------------
 2001-06-22 16:42:44+09
(1 row)

    sum
-----------
 617001234
(1 row)

          now
------------------------
 2001-06-22 16:42:54+09
(1 row)

yohgaki=#

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

Took 12 seconds. (About 4.3 times faster)

yohgaki=# select now(); select sum(TarifDurationAir-CallDuration) from callbase;
select now();
          now
------------------------
 2001-06-22 16:43:45+09
(1 row)

    sum
------------
 2222004444
(1 row)

          now
------------------------
 2001-06-22 16:43:57+09
(1 row)

yohgaki=#

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

NOTICE:  QUERY PLAN:

Aggregate  (cost=74982.03..78732.04 rows=50000 width=12)
  ->  Group  (cost=74982.03..76232.04 rows=500001 width=12)
        ->  Sort  (cost=74982.03..74982.03 rows=500001 width=12)
              ->  Seq Scan on callbase  (cost=0.00..9673.01 rows=500001
width=12)

EXPLAIN
yohgaki=#

I don't define any indexes for rows.

>
>
> 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.

Took 18 second. (About 6.3 times faster)

          now
------------------------
 2001-06-22 16:46:52+09
(1 row)

 numberid |    ?column?
----------+-----------------
          | 4444.0000000000
(1 row)

          now
------------------------
 2001-06-22 16:47:10+09
(1 row)

yohgaki=#

Took 18 seconds. However, I think this result is not fair to compare
since I have meaning less data in the table and have only 1 result. (Mamin
has more than 3000 thousands lines of outputs for terminal)

Do you really have enough memory and good options for Postgres?
Is  your kernel and options are optimized enough?
Did test this on busy server?
My test PC is running many servers (httpd, smbd, named, qmail, etc,), but
this PC is not used by anyone but me.

BTW, I always have primary key in a table, so I added primary key to the
table. I don't have index other than primary key. Query would finish faster
if I add some indexes.
If you have primary key, I think you'll have better results.

Regards,
--
Yasuo Ohgaki