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 /*
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
