pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Never ending delete story
2+ messages / 2 participants
[nested] [flat]

* Never ending delete story
@ 2005-04-06 21:05 Jarosław Pałka <jaroslaw.palka@mrt-system.pl>
  2005-04-11 06:40 ` Re: Never ending delete story Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Jarosław Pałka @ 2005-04-06 21:05 UTC (permalink / raw)
  To: pgsql-performance

Hi!!!

We are running PostgreSQL server version 7.4.6 on RedHat 9 (Shrike) on 
single Pentium 4 (2.66 GHz) box with SCSI disc and 512 MB RAM.
Our database contains several tables (small size) and one special table 
with ~1000000 records (it contains log entries from system activity).We 
decided that its time to do a little clean-up and  it's still running 
(for about  12 hours) and it seems that it won't stop :((

Here schema of largest table:
                      Table "public.activities"
       Column       |            Type             | Modifiers
-------------------+-----------------------------+-----------
  act_id            | bigint                      | not null
  act_type          | character varying(32)       | not null
  act_activity_date | timestamp without time zone | not null
  act_synch_date    | timestamp without time zone |
  act_state         | character varying(32)       |
  act_mcn_id        | bigint                      |
  act_mcn_alarm     | character varying(16)       |
  act_cmd_id        | bigint                      |
  act_ctr_id        | bigint                      |
  act_emp_id        | bigint                      |
  act_parent_id     | bigint                      |
  act_rpt_id        | bigint                      |
Indexes:
     "activities_pkey" primary key, btree (act_id)
     "activities_act_cmd_id" btree (act_cmd_id)
     "activities_act_ctr_id" btree (act_ctr_id)
     "activities_act_state_idx" btree (act_state)
     "activities_act_type_idx" btree (act_type)
Foreign-key constraints:
     "fk7a1b3bed494acc46" FOREIGN KEY (act_ctr_id) REFERENCES 
controllers(ctr_id)
     "fk7a1b3bed4c50f03f" FOREIGN KEY (act_emp_id) REFERENCES 
employees(emp_id)
     "fk7a1b3bed48e1ca8d" FOREIGN KEY (act_cmd_id) REFERENCES 
commands(cmd_id)
     "fk7a1b3bed5969e16f" FOREIGN KEY (act_mcn_id) REFERENCES 
machines(mcn_id)
     "fk7a1b3bedf3fd6e40" FOREIGN KEY (act_parent_id) REFERENCES 
activities(act_id)
     "fk7a1b3bed62ac0851" FOREIGN KEY (act_rpt_id) REFERENCES

and our killer delete:

mrt-vend2-jpalka=# explain delete from activities where 
act_type='controller-activity' and act_ctr_id in (select ctr_id from 
controllers where ctr_opr_id in (1,2));
                                                QUERY PLAN 

--------------------------------------------------------------------------------------------------------
  Merge IN Join  (cost=9.87..17834.97 rows=84933 width=6)
    Merge Cond: ("outer".act_ctr_id = "inner".ctr_id)
    ->  Index Scan using activities_act_ctr_id on activities 
(cost=0.00..34087.59 rows=402627 width=14)
          Filter: ((act_type)::text = 'controller-activity'::text)
    ->  Sort  (cost=9.87..10.09 rows=89 width=8)
          Sort Key: controllers.ctr_id
          ->  Seq Scan on controllers  (cost=0.00..6.99 rows=89 width=8)
                Filter: ((ctr_opr_id = 1) OR (ctr_opr_id = 2))
(8 rows)
reports(rpt_id)

Table controllers contains about 200 records.Is it problem with large 
number of foreign keys in activities table?

Can you help me?

Thanks,
Jaroslaw Palka



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Never ending delete story
  2005-04-06 21:05 Never ending delete story Jarosław Pałka <jaroslaw.palka@mrt-system.pl>
@ 2005-04-11 06:40 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2005-04-11 06:40 UTC (permalink / raw)
  To: Jarosław Pałka <jaroslaw.palka@mrt-system.pl>; +Cc: pgsql-performance

=?UTF-8?B?SmFyb3PFgmF3IFBhxYJrYQ==?= <jaroslaw.palka@mrt-system.pl> writes:
> We are running PostgreSQL server version 7.4.6 on RedHat 9 (Shrike) on 
> single Pentium 4 (2.66 GHz) box with SCSI disc and 512 MB RAM.
> Our database contains several tables (small size) and one special table 
> with ~1000000 records (it contains log entries from system activity).We 
> decided that its time to do a little clean-up and  it's still running 
> (for about  12 hours) and it seems that it won't stop :((

Do you have any foreign keys linking *to* (not from) this table?
If so, they probably need indexes on the far end.  Also check for
datatype discrepancies.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-04-11 06:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-06 21:05 Never ending delete story Jarosław Pałka <jaroslaw.palka@mrt-system.pl>
2005-04-11 06:40 ` Tom Lane <tgl@sss.pgh.pa.us>

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