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

Paul Mamin wrote:
>
[table definition snipped]

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

This is 64MB not 64kB - look at the "top" output. Probably too big for a
128MB machine - you're limiting Linux's ability to manage your memory.
I'm assuming you're not planning to have thousands of clients connecting simultaneously.

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

Are you getting a lot of disk activity with this?

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

OK - sequential scan - what you'd expect since you need to access all
the entries anyway. The cost estimates are the same for the
aggregate,group and sort.

I'm no expert on performance tuning, but I'd refer you to ch 3.4 / 3.5
of the Administrator's manual - runtime config and kernel resources. I'd
leave the -B option alone and rerun - I'd guess performance won't get
any worse. Then try increasing the sort memory and/or buffers gradually
until you get a feel for how these affect the system.

I'd use "vmstat" to monitor the system while the query is running. You
shouldn't be seeing a lot of disk activity.

If nothing leaps out at you, and you can get me details of the
distribution of values for numberid by lunchtime (it's 8am here) I'll
try your query on similar hardware here.

HTH

- Richard Huxton