From: ries@jongert.nl
To: pgsql-sql@postgresql.org
Subject: is this explain good or bad???
Date: Fri, 19 Sep 2003 08:33:11 +0200
Message-ID: <002b01c37e77$e65b7fb0$fd01000a@IT001> (raw)
Hey guys,
I'm not sure if this is good or bad but when I want to have a record count
of a view it takes at least 75 seconds to complete (PII 1.4Ghz 256Mb RAM).
Also when I do a record count of one single table (same table but not
joined) it just takes a long time. Firt I want to make sure that the DB is
setup correctly before I jump to HW issues.
This is my sq_logfile table
explain select count(*) from sq_logfile;
Aggregate (cost=35988.07..35988.07 rows=1 width=0)
-> Seq Scan on sq_logfile (cost=0.00..33493.86 rows=997686 width=0)
This is ,y sq_flogfile view based on sq_flogfile
explain select count(*) from sq_flogile;
Aggregate (cost=128282.68..128282.68 rows=1 width=40)
-> Hash Join (cost=8.65..125788.46 rows=997686 width=40)
-> Hash Join (cost=6.49..105832.58 rows=997686 width=36)
-> Hash Join (cost=4.75..88371.34 rows=997686 width=32)
-> Hash Join (cost=3.69..68416.56 rows=997686
width=28)
-> Hash Join (cost=2.58..50955.94 rows=997686
width=24)
-> Seq Scan on sq_logfile sl
(cost=0.00..33493.86 rows=997686 width=20)
-> Hash (cost=2.26..2.26 rows=126 width=4)
-> Seq Scan on sq_contenttypes ct
(cost=0.00..2.26 rows=126 width=4)
-> Hash (cost=1.09..1.09 rows=9 width=4)
-> Seq Scan on sq_requestmethods rm
(cost=0.00..1.09 rows=9 width=4)
-> Hash (cost=1.05..1.05 rows=5 width=4)
-> Seq Scan on sq_hierarchycodes hc
(cost=0.00..1.05 rows=5 width=4)
-> Hash (cost=1.59..1.59 rows=59 width=4)
-> Seq Scan on sq_resultcodes rc (cost=0.00..1.59
rows=59 width=4)
-> Hash (cost=1.93..1.93 rows=93 width=4)
-> Seq Scan on sq_clientaddrfqdn cafqdn (cost=0.00..1.93
rows=93 width=4)
<------------------->
CREATE TABLE sq_logfile (
id SERIAL8,
stime NUMERIC(14,3),
tstime TIMESTAMP,
duration INTEGER,
client_addr_dotted INET,
client_addr_fqdn_id INTEGER DEFAULT 0 NOT NULL REFERENCES
sq_clientaddrfqdn (id),
resultcode_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_resultcodes(id),
requestsize INTEGER,
requestmethod_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_requestmethods
(id),
url TEXT,
rfc931 TEXT,
hierarchycode_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_hierarchycodes
(id),
hierarchycode TEXT,
contenttype_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_contenttypes (id),
PRIMARY KEY(id)
);
-- Everything is sorted bu date/time so we need a index???
CREATE INDEX idx_sq_logfile1 ON sq_logfile (tstime);
CREATE INDEX idx_sq_logfile2 ON sq_logfile (tstime, client_addr_dotted);
CREATE INDEX idx_sq_logfile3 ON sq_logfile (tstime, rfc931);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ries@jongert.nl
Subject: Re: is this explain good or bad???
In-Reply-To: <002b01c37e77$e65b7fb0$fd01000a@IT001>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox