explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IBQR

Settings
# exclusive inclusive rows x rows loops node
1. 26,624.627 111,855.055 ↓ 0.0 0 1

Update on visit_occurrence t (cost=1,989,689.73..2,495,092.70 rows=33,381,175 width=369) (actual time=111,855.055..111,855.055 rows=0 loops=1)

2. 6,293.893 85,230.428 ↑ 56.8 588,193 1

Merge Join (cost=1,989,689.73..2,495,092.70 rows=33,381,175 width=369) (actual time=71,171.931..85,230.428 rows=588,193 loops=1)

  • Merge Cond: (t.visit_source_concept_id = cr.concept_id_1)
3. 9,202.563 17,087.797 ↑ 1.0 2,832,609 1

Sort (cost=857,155.81..864,256.27 rows=2,840,184 width=353) (actual time=12,947.557..17,087.797 rows=2,832,609 loops=1)

  • Sort Key: t.visit_source_concept_id
  • Sort Method: external merge Disk: 343072kB
4. 7,885.234 7,885.234 ↑ 1.0 2,832,609 1

Seq Scan on visit_occurrence t (cost=0.00..86,753.84 rows=2,840,184 width=353) (actual time=0.017..7,885.234 rows=2,832,609 loops=1)

5. 5,480.028 61,848.738 ↓ 1.6 3,805,623 1

Materialize (cost=1,121,265.47..1,133,342.89 rows=2,415,484 width=20) (actual time=53,107.098..61,848.738 rows=3,805,623 loops=1)

6. 9,712.353 56,368.710 ↓ 1.3 3,217,549 1

Sort (cost=1,121,265.47..1,127,304.18 rows=2,415,484 width=20) (actual time=53,107.091..56,368.710 rows=3,217,549 loops=1)

  • Sort Key: cr.concept_id_1
  • Sort Method: external merge Disk: 154528kB
7. 11,180.854 46,656.357 ↓ 2.0 4,933,501 1

Hash Join (cost=365,291.83..815,638.29 rows=2,415,484 width=20) (actual time=7,663.587..46,656.357 rows=4,933,501 loops=1)

  • Hash Cond: (cr.concept_id_2 = c.concept_id)
8. 27,865.517 29,033.386 ↑ 1.0 4,933,501 1

Bitmap Heap Scan on concept_relationship cr (cost=118,607.57..492,218.08 rows=5,022,840 width=14) (actual time=1,220.122..29,033.386 rows=4,933,501 loops=1)

  • Recheck Cond: ((relationship_id)::text = 'Maps to'::text)
  • Heap Blocks: exact=226137
9. 1,167.869 1,167.869 ↑ 1.0 4,933,501 1

Bitmap Index Scan on idx_concept_relationship_id_3 (cost=0.00..117,351.86 rows=5,022,840 width=0) (actual time=1,167.868..1,167.869 rows=4,933,501 loops=1)

  • Index Cond: ((relationship_id)::text = 'Maps to'::text)
10. 3,078.066 6,442.117 ↑ 1.0 2,951,876 1

Hash (cost=195,069.59..195,069.59 rows=2,969,253 width=10) (actual time=6,442.116..6,442.117 rows=2,951,876 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 24026kB
11. 3,364.051 3,364.051 ↑ 1.0 2,951,876 1

Seq Scan on concept c (cost=0.00..195,069.59 rows=2,969,253 width=10) (actual time=0.029..3,364.051 rows=2,951,876 loops=1)

  • Filter: ((standard_concept)::text = 'S'::text)
  • Rows Removed by Filter: 3232289
Planning time : 15.876 ms
Execution time : 117,795.708 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint fpk_visit_concept 5,419.239 ms 588193 0.009 ms
set_updated_at_timestamp 2,223.945 ms 588193 0.004 ms