From: | Yaroslav Dmitriev <yar(at)warlock(dot)ru> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | []performance issues |
Date: | 2002-08-02 08:59:19 |
Message-ID: | 3D4A49E7.6090405@warlock.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2002-08-02 09:15:38 | Re: []performance issues |
Previous Message | Christopher Kings-Lynne | 2002-08-02 08:50:13 | Re: getpid() function |
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2002-08-02 09:15:38 | Re: []performance issues |
Previous Message | Christopher Kings-Lynne | 2002-08-02 08:50:13 | Re: getpid() function |