Thread: Performance issue

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 /*

On 27/04/2004 10:12 Michael L. Hostbaek wrote:
> [snip]
> 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...

You would probably get better answers on the correct list but my guess is 
that your fsm setting might be too low for the number of dead tuples you 
are trying to reclaim so the vacuum is not clearing all the dead stuff 
out. There's plenty of stuff about this in the archives and the docs.


-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for 
Business             |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

"Michael L. Hostbaek" <mich@the-lab.org> writes:
> 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...

You should vacuum in between ... or even better, do the deletion with
TRUNCATE.
        regards, tom lane