Re: delete taking long time

Hi Andreas,

shared_buffers is 512 MB.
I had to reduce the ts to only 10 minutes, just to be able to finish
promptly. Results are bellow.

select count(uid) from connection where uid in (select uid from connection
where ts > '2016-03-10 01:00:00' and ts < '2016-03-10 01:10:00');

count

-------

2156

(1 row)

explain (analyze) delete from connection where uid in (select uid from
connection where ts > '2016-03-10 01:00:00' and ts < '2016-03-10 01:10:00');

-------------------------------------------------------------------------------------------------------------------------------------------------

Delete on connection (cost=0.43..174184.31 rows=7756 width=12) (actual
time=529.739..529.739 rows=0 loops=1)

-> Nested Loop (cost=0.43..174184.31 rows=7756 width=12) (actual
time=0.036..526.295 rows=2156 loops=1)

-> Seq Scan on connection connection_1 (cost=0.00..115684.55
rows=7756 width=24) (actual time=0.020..505.012 rows=2156 loops=1)

Filter: ((ts > '2016-03-10 01:00:00'::timestamp without time
zone) AND (ts < '2016-03-10 01:10:00'::timestamp without time zone))

Rows Removed by Filter: 3108811

-> Index Scan using connection_pkey on connection
(cost=0.43..7.53 rows=1 width=24) (actual time=0.009..0.010 rows=1
loops=2156)

Index Cond: ((uid)::text = (connection_1.uid)::text)

Planning time: 0.220 ms

Trigger for constraint dns_uid_fkey: time=133.046 calls=2156

Trigger for constraint files_uid_fkey: time=39780.799 calls=2156

Trigger for constraint http_uid_fkey: time=99300.851 calls=2156

Trigger for constraint notice_uid_fkey: time=128.653 calls=2156

Trigger for constraint snmp_uid_fkey: time=59.491 calls=2156

Trigger for constraint ssl_uid_fkey: time=74.052 calls=2156

Trigger for constraint weird_uid_fkey: time=25868.651 calls=2156

Execution time: 165880.419 ms

(16 rows)

>
> Given the lack of indexes on the one table that is shown I suspect this is
> the most likely cause (FK + indexes)
>

> right, there should be an index on ts. If it is a 9.5 AND the rows are
ordered
> on ts a BRIN-index would be fine.
> If not - a normal index. How many rows contains the table?

count

---------

3108811

(1 row)

> There are SEVEN FKs against that table…I would bet that’s 50% of the
duration. The lack of an index,

> perhaps an issue, but

> With that many FK references plus that many rows…the transaction log
could easily blow out and start

> paging to disk.

I think you are right, fk seem to take the biggest chunk of time from the
hole delete operation. I made a test with 10.000 rows, it took 12 minutes.
20.000 rows took about 25 minutes to delete.

Regards to all.

On 16 March 2016 at 01:12, 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.
>
>
>
> > 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?
>
> How large is shared_buffers set?
>
> Can you show us the explain (analyse)?
>
>
> Regards, Andreas
> --
> Andreas Kretschmer
> http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>