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