explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1pzf

Settings
# exclusive inclusive rows x rows loops node
1. 205,465.056 297,017.547 ↓ 0.0 0 1

Update on measurement t (cost=1,920,175.72..1,962,044.90 rows=1,245,518 width=229) (actual time=297,017.546..297,017.547 rows=0 loops=1)

2. 14,455.702 91,552.491 ↓ 3.9 4,909,577 1

Merge Join (cost=1,920,175.72..1,962,044.90 rows=1,245,518 width=229) (actual time=60,128.702..91,552.491 rows=4,909,577 loops=1)

  • Merge Cond: (t.measurement_source_concept_id = cr.concept_id_1)
3. 22,174.928 35,177.942 ↑ 1.0 4,909,577 1

Sort (cost=1,281,155.02..1,293,480.28 rows=4,930,103 width=213) (actual time=22,555.654..35,177.942 rows=4,909,577 loops=1)

  • Sort Key: t.measurement_source_concept_id
  • Sort Method: external merge Disk: 649368kB
4. 13,003.014 13,003.014 ↑ 1.0 4,909,577 1

Seq Scan on measurement t (cost=0.00..227,566.03 rows=4,930,103 width=213) (actual time=0.012..13,003.014 rows=4,909,577 loops=1)

5. 4,461.877 41,918.847 ↓ 96.0 4,987,616 1

Sort (cost=638,336.99..638,466.82 rows=51,933 width=20) (actual time=37,566.673..41,918.847 rows=4,987,616 loops=1)

  • Sort Key: cr.concept_id_1
  • Sort Method: quicksort Memory: 10458kB
6. 4,775.574 37,456.970 ↓ 1.8 94,537 1

Hash Join (cost=247,474.04..634,269.50 rows=51,933 width=20) (actual time=1,495.686..37,456.970 rows=94,537 loops=1)

  • Hash Cond: (cr.concept_id_2 = c.concept_id)
7. 31,238.704 32,342.810 ↑ 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,156.855..32,342.810 rows=4,933,501 loops=1)

  • Recheck Cond: ((relationship_id)::text = 'Maps to'::text)
  • Heap Blocks: exact=226137
8. 1,104.106 1,104.106 ↑ 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,104.105..1,104.106 rows=4,933,501 loops=1)

  • Index Cond: ((relationship_id)::text = 'Maps to'::text)
9. 80.353 338.586 ↓ 1.3 83,630 1

Hash (cost=128,068.47..128,068.47 rows=63,839 width=10) (actual time=338.585..338.586 rows=83,630 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 4618kB
10. 209.076 258.233 ↓ 1.3 83,630 1

Bitmap Heap Scan on concept c (cost=2,616.01..128,068.47 rows=63,839 width=10) (actual time=57.155..258.233 rows=83,630 loops=1)

  • Recheck Cond: ((domain_id)::text = 'Measurement'::text)
  • Filter: ((standard_concept)::text = 'S'::text)
  • Rows Removed by Filter: 51477
  • Heap Blocks: exact=5974
11. 49.157 49.157 ↓ 1.0 135,107 1

Bitmap Index Scan on idx_concept_domain_id (cost=0.00..2,600.05 rows=132,749 width=0) (actual time=49.156..49.157 rows=135,107 loops=1)

  • Index Cond: ((domain_id)::text = 'Measurement'::text)
Planning time : 9.943 ms
Execution time : 348,386.952 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint fpk_measurement_concept 47,420.239 ms 4909577 0.010 ms
set_updated_at_timestamp 18,730.669 ms 4909577 0.004 ms