Re: []performance issues

times change if you do
"SELECT COUNT(1) FROM stats" ?

--
:: Sergio A. Kessler ::
Linux user #64005 - http://counter.li.org

"Yaroslav Dmitriev" <yar@warlock.ru> escribi� en el mensaje
news:3D4A49E7.6090405@warlock.ru...
> Hello,
>
> Sorry if it's wrong list for the question. Could you suggest some tweaks
> to the PostgreSQL 7.2.1 to handle the following types of tables faster?
>
> Here we have  table "stats" with  something over one millon records.
> Obvious "SELECT COUNT(*) FROM stats " takes over 40 seconds to execute,
> and this amount of time does not shorten considerably in subsequent
> similar requests. All the databases are vacuumed nightly.
>
> CREATE TABLE "stats" (
>    "url" varchar(50),
>    "src_port" varchar(10),
>    "ip" varchar(16),
>    "dst_port" varchar(10),
>    "proto" varchar(10),
>    "size" int8,
>    "login" varchar(20),
>    "start_date" timestamptz,
>    "end_date" timestamptz,
>    "aggregated" int4
> );
> CREATE  INDEX "aggregated_stats_key" ON "stats" ("aggregated");
> CREATE  INDEX "ip_stats_key" ON "stats" ("ip");
>
> stats=> explain select count(*) from stats;
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=113331.10..113331.10 rows=1 width=0)
>   ->  Seq Scan on stats  (cost=0.00..110085.28 rows=1298328 width=0)
>
> EXPLAIN
> stats=> select count(*) from stats;
>   count
> ---------
>  1298328
> (1 row)
>
> The system is FreeBSD-4.6-stable, softupdates on, Athlon XP 1500+, 512 Mb
DDR, ATA 100 HDD.
>
> Thanks in advance,
> Yar
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org