X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 06C28D1B4EC for ; Fri, 19 Sep 2003 06:35:25 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 68601-02 for ; Fri, 19 Sep 2003 03:34:54 -0300 (ADT) Received: from jongert.nl (jongert.nl [194.109.59.87]) by svr1.postgresql.org (Postfix) with ESMTP id 2FDDDD1B4E9 for ; Fri, 19 Sep 2003 03:34:52 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by jongert.nl (Postfix) with SMTP id BA82A3C81A2; Fri, 19 Sep 2003 08:34:50 +0200 (CEST) X-AV-Checked: Fri Sep 19 08:34:50 2003 jongert.nl Received: from IT001 (IT001.int.jongert.nl [10.0.1.253]) by jongert.nl (Postfix) with SMTP id 6575C3C80B1 for ; Fri, 19 Sep 2003 08:34:50 +0200 (CEST) From: To: Subject: is this explain good or bad??? Date: Fri, 19 Sep 2003 08:33:11 +0200 Message-ID: <002b01c37e77$e65b7fb0$fd01000a@IT001> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/182 X-Sequence-Number: 15160 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);