From: Michael L. Hostbaek <mich@the-lab.org>
To: pgsql-sql@postgresql.org
Subject: Performance issue
Date: Tue, 27 Apr 2004 11:12:52 +0200
Message-ID: <20040427091252.GA93126@mich2.itxmarket.com> (raw)
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 /*
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: mich@the-lab.org
Subject: Re: Performance issue
In-Reply-To: <20040427091252.GA93126@mich2.itxmarket.com>
* 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