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
