Search Postgresql Archives

Re: Index seems "lost" after consecutive deletes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, 2016-06-13 at 22:41 -0300, Edson Richter wrote:
> Em 13/06/2016 22:33, Edson Richter escreveu:
> > I've a table "A" with 4,000,000 records.
> > 
> > I've decided to delete records from oldest to newest but I can't 
> > delete records that have references in tables "B", "C" or "D".
> > 
> > 
> > so, I've
> > 
> > 
> > with qry as (
> > 
> >     select A.id
> > 
> >       from A
> > 
> >     where not exists (select 1 from B where B.a_id = A.id)
> > 
> >        and not exists (select 1 from C where C.a_id = A.id)
> > 
> >        and not exists (select 1 from D where D.a_id = A.id)
> > 
> >        and A.creation_date < (now()::date - interval '12 month')
> > 
> >   order by A.id DESC
> > 
> >   limit 2000
> > 
> > )
> > 
> > delete from A where id in (select id from qry);
> > 
> > 
> > All three referenced tables have indexes (B.a_id; C.a_id; D.a_id)
> > in 
> > order to make query faster.
> > 
> > So for first 2 million rows it worked really well, taking about 1 
> > minute to delete each group of 2000 records.
> > 
> > Then, after a while I just started to get errors like:
> > 
> > 
> > ERROR: update or delete in "A" violates foreign key "fk_C_A" in
> > "C".
> > 
> > DETAIL:  Key (id)=(3240124) is still referenced by table "C".
> > 
> > 
> > Seems to me that indexes got lost in the path - the query is
> > really 
> > specific and no "C" referenced records can be in my deletion.
> > 
> > Has anyone faced a behavior like this?
> > 
> > Am I doing something wrong?
> > 
> > 
> 
> Of course:
> Version string    PostgreSQL 9.4.8 on x86_64-unknown-linux-gnu,
> compiled 
> by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
> Oracle Linux 7 x64 with all updates. Running on EXT4 file system.
> Computer is Dell R420 with mirrored disks, 80GB of RAM (database has
>
> 40GB in total).
> 
> Sorry for not putting the info in the first e-mail.
> 
> Edson
> 
> 

What does:-

SELECT COUNT(*) FROM C WHERE C.a_id = 3240124;

return?

Is it a many-to-one or a one-to-one relationship?




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux