X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2DA21536B3 for ; Thu, 14 Apr 2005 07:48:29 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 97065-03 for ; Thu, 14 Apr 2005 10:48:19 +0000 (GMT) Received: from krogh.officenet.no (unknown [128.39.244.194]) by svr1.postgresql.org (Postfix) with ESMTP id 542AB53691 for ; Thu, 14 Apr 2005 07:48:18 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by krogh.officenet.no (Postfix) with ESMTP id 659E82C986A for ; Thu, 14 Apr 2005 12:48:22 +0200 (CEST) From: Andreas Joseph Krogh Organization: OfficeNet AS To: pgsql-sql@postgresql.org Subject: Very low performance on table with only 298 rows User-Agent: KMail/1.8 MIME-Version: 1.0 Content-Disposition: inline X-UID: 8762 X-Length: 3652 Date: Thu, 14 Apr 2005 12:48:21 +0200 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200504141248.21783.andreak@officenet.no> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/185 X-Sequence-Number: 21078 nbeweb=3D> EXPLAIN select count(*) from onp_web_index; QUERY PLAN =2D------------------------------------------------------------------------= =2D--- Hi all. I have a problem with a table which only has 298 rows in it. A select count= (*)=20 takes forever and CTRL-C also takes a long time canceling the query. There= =20 are only 298 rows in the table when count(*) returns. How come it takes suc= h=20 a long time counting only 298 rows? Here is EXPLAIN ANALYZE: nbeweb=3D> EXPLAIN ANALYZE select count(*) from onp_web_index; QUERY PLAN =2D------------------------------------------------------------------------= =2D--------------------------------------------------------- Aggregate (cost=3D205759.52..205759.52 rows=3D1 width=3D0) (actual=20 time=3D179748.910..179748.913 rows=3D1 loops=3D1) -> Seq Scan on onp_web_index (cost=3D0.00..205455.41 rows=3D121641 wid= th=3D0)=20 (actual time=3D179735.956..179747.821 rows=3D298 loops=3D1) Total runtime: 179748.993 ms (3 rows) Here is the schema: nbeweb=3D> \d onp_web_index; Table "public.onp_web_index" Column | Type | Modifiers =2D------------+-------------------+---------------------------------------= =2D----------------------- id | integer | not null default=20 nextval('public.onp_web_index_id_seq'::text) starturl_id | integer | not null lang_id | integer | not null url_host | character varying | not null url_path | character varying | not null title | character varying | not null content | tsvector | not null plain_text | character varying | not null Indexes: "onp_web_index_pkey" primary key, btree (id) "onp_web_index_url_host_key" unique, btree (url_host, url_path) "onp_web_index_idx" gist (content) =46oreign-key constraints: "$2" FOREIGN KEY (lang_id) REFERENCES code(id) "$1" FOREIGN KEY (starturl_id) REFERENCES onp_web_index_starturl(id) ON= =20 DELETE CASCADE Any hints on how I can "fix" this lousy performance? =2D-=20 Andreas Joseph Krogh Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc =2D-----------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Sk=F8yen | somebody else doing it wrong, without | 0213 Oslo | comment. | NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | =2D-----------------------+---------------------------------------------+