Re: delete taking long time

> And...from a db design perspective, a table referenced by 7 FKs shouldn't
be
> having this type
> of delete run against it...it's just too expensive if it must happen
> routinely.

The connection table should never have any data deleted, only insertions
are performed.
I had logged using ssh to the db server, forgot to plugin my laptop and it
went to sleep mode while running an insert script from external data source
- a bro ids file. The shell logged me out and the insert statement never
completed ( about 10 % of the data never got inserted). I should know
better.

Kind regards to all of you.

On 16 March 2016 at 12:31, Mike Sofen <msofen(at)runbox(dot)com> wrote:

> I agree with Andreas (indexes) - 10 minutes to delete 10k rows is about 9.5
> minutes too long.
> Either the "select" part of the query can't find the rows quickly or the FK
> burden is crushing the life out of it.
>
> If every involved table has an index on their Primary Key then the 10k row
> delete should take
> maybe 30-60 seconds. Highly dependent on how many FK rows are involved.
>
> And...from a db design perspective, a table referenced by 7 FKs shouldn't
> be
> having this type
> of delete run against it...it's just too expensive if it must happen
> routinely. This is where
> de-normalization might be called for, to collapse some of those references,
> or a shift to
> stored functions that maintain integrity versus the declared foreign keys
> maintaining it.
>
> Mike S.
>
> -----Original Message-----
> From: Andreas Kretschmer
> Sent: Wednesday, March 16, 2016 4:58 AM
>
> ivo liondov <ivo(dot)liondov(at)gmail(dot)com> wrote:
>
> >
> > 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))
>
>
> there is no index on the ts-column.
>
>
>
>
>
>
>
>
> >
> > 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
>
> i guess there are no indexes for this tables and the relevant columns
>
>
> > 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.
>
> create the missing indexes now and come back with the new duration.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>