X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2696DD1B4A0 for ; Tue, 27 Apr 2004 06:12:55 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 92640-04 for ; Tue, 27 Apr 2004 06:12:54 -0300 (ADT) Received: from mail2.itxmarket.com (fw.itxmarket.com [213.11.40.109]) by svr1.postgresql.org (Postfix) with ESMTP id 659D7D1B475 for ; Tue, 27 Apr 2004 06:12:51 -0300 (ADT) Received: from freebsdcluster.net (mich2.itxmarket.com [192.168.2.26]) by mail2.itxmarket.com (Postfix) with ESMTP id 4C93937DE5 for ; Tue, 27 Apr 2004 11:12:52 +0200 (CEST) Received: by freebsdcluster.net (Postfix, from userid 1001) id 4077522825; Tue, 27 Apr 2004 11:12:52 +0200 (CEST) Date: Tue, 27 Apr 2004 11:12:52 +0200 From: "Michael L. Hostbaek" To: pgsql-sql@postgresql.org Subject: Performance issue Message-ID: <20040427091252.GA93126@mich2.itxmarket.com> Mail-Followup-To: "Michael L. Hostbaek" , pgsql-sql@postgresql.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline X-Operating-System: FreeBSD 5.2.1-RELEASE i386 X-PGP-Key: http://www.freebsdcluster.org/~mich/ User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/294 X-Sequence-Number: 17669 Howdy, I am expiriencing some performance issues, on a table in my postgres db. I cron script is being run every night (while very low db activity), that deletes all rows from the table, and injects a bunch of new data... Every day between 60.000 and 100.000 rows. Info: ppdb=> select version(); version ------------------------------------------------------------------------------------------------ PostgreSQL 7.4.1 on i386-portbld-freebsd5.2, compiled by GCC cc (GCC) 3.3.3 [FreeBSD] 20031106 (1 row) ppdb=> \d itxhas Table "public.itxhas" Column | Type | Modifiers -------------+-----------------------------+---------------------------------------------------- hasid | integer | not null default nextval('itxhas_hasid_seq'::text) partno | character varying(60) | mfg | character varying(25) | qty | character varying(20) | condition | character varying(20) | gescode | character varying(10) | cmup | numeric(14,2) | create_date | timestamp without time zone | posted_date | timestamp without time zone | status | character varying(15) | company_id | integer | die_date | timestamp without time zone | Indexes: "itx_create_date_idx" btree (create_date Every time I need to select something from this table, I feel it takes way too long.. I have run vacuum analyze without improvement. ppdb=> explain select count(*) from itxhas; QUERY PLAN -------------------------------------------------------------------- Aggregate (cost=78472.86..78472.86 rows=1 width=0) -> Seq Scan on itxhas (cost=0.00..78253.09 rows=87909 width=0) (2 rows) ppdb=> explain analyze select count(*) from itxhas; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=78472.86..78472.86 rows=1 width=0) (actual time=24242.717..24242.719 rows=1 loops=1) -> Seq Scan on itxhas (cost=0.00..78253.09 rows=87909 width=0) (actual time=23763.883..24122.221 rows=87909 loops=1) Total runtime: 24242.844 ms (3 rows) Is this normal ? If I run the same select on another table in the same database with ~40.000 rows, it takes approx 820.00ms... Any ideas ? /mich -- Best Regards, Michael L. Hostbaek */ PGP-key available upon request /*