SeisComP3/seiscomp3 — pull request #5 by fabienengels (2014-10-21 17:52:46+00:00)
Title: Add missing indexes on _parent_oid fields

As our database (~60M objects) is growing, we start to have a bottleneck while deleting objects. An example :

```sql
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Delete on object (cost=0.56..8.58 rows=1 width=6) (actual time=0.092..0.092 rows=0 loops=1)
-> Index Scan using object_pkey on object (cost=0.56..8.58 rows=1 width=6) (actual time=0.022..0.022 rows=1 loops=1)
Index Cond: (_oid = 59111468)
...
Trigger for constraint event__parent_oid_fkey: time=30.287 calls=1
...
Trigger for constraint magnitude__parent_oid_fkey: time=62.454 calls=1
...
Trigger for constraint origin__parent_oid_fkey: time=46.817 calls=1
...
Trigger for constraint pick__parent_oid_fkey: time=1452.192 calls=1
...
Trigger for constraint stationmagnitude__parent_oid_fkey: time=308.730 calls=1
...
Trigger for constraint amplitude__parent_oid_fkey: time=5662.127 calls=1
Total runtime: 7639.337 ms
(117 rows)
```

It takes almost **8 seconds** to delete an object but we can see a lot of time is lost due to _parent_oid fields. After adding missing indexes, it takes only **6 ms** :

```sql
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Delete on object (cost=0.56..8.58 rows=1 width=6) (actual time=0.029..0.029 rows=0 loops=1)
-> Index Scan using object_pkey on object (cost=0.56..8.58 rows=1 width=6) (actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (_oid = 59111466)
...
Trigger for constraint event__parent_oid_fkey: time=0.036 calls=1
...
Trigger for constraint magnitude__parent_oid_fkey: time=0.037 calls=1
...
Trigger for constraint origin__parent_oid_fkey: time=0.042 calls=1
...
Trigger for constraint pick__parent_oid_fkey: time=0.036 calls=1
...
Trigger for constraint stationmagnitude__parent_oid_fkey: time=0.040 calls=1
...
Trigger for constraint amplitude__parent_oid_fkey: time=0.105 calls=1
...
Total runtime: 5.569 ms
(117 rows)
```