From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> |
Cc: | ivo liondov <ivo(dot)liondov(at)gmail(dot)com>, "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: delete taking long time |
Date: | 2016-03-16 01:24:41 |
Message-ID: | CAKFQuwZ_E08T37A_eTtky2pQ95Ruc65MyywLp-yKjhVPUdgEMw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Tuesday, March 15, 2016, Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>
wrote:
>
> >
> > I am trying to delete the connections with date 2016-03-10 by using the
> > following:
> >
> >
> > delete from connection where uid in (select uid from connection where ts
> >
> > '2016-03-10 00:30:00');
>
> try to rewrite that to :
>
> delete from connection where ts > '2016-03-10 00:30:00';
>
> It's simpler - and (maybe) faster.
>
>
>
It also gives a different answer...
>
> > There are around 800.000 records matching this rule, and seems to be
> taking
> > an awful lot of time - 4 hours and counting. What could be the reason for
> > such a performance hit and how could I optimise this for future cases?
> >
> > Regards.
>
> the db has to touch such many rows, and has to write the transaction log.
> And
> update every index. And it has to check the referenced tables for the
> constraints. Do you have proper indexes?
Given the lack of indexes on the one table that is shown I suspect this is
the most likely cause (FK + indexes)
>
>
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-03-16 01:25:59 | Re: delete taking long time |
Previous Message | Andreas Kretschmer | 2016-03-16 01:12:27 | Re: delete taking long time |