explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TSXO

Settings
# exclusive inclusive rows x rows loops node
1. 3.849 293,801.417 ↓ 133.6 53,454 1

Append (cost=4,740,246.31..4,740,289.95 rows=400 width=118) (actual time=293,646.901..293,801.417 rows=53,454 loops=1)

  • Buffers: shared hit=1,165,766 read=375,675 written=3,354
2.          

CTE r

3. 587.132 175,819.422 ↓ 1.0 221,467 1

Hash Join (cost=575,685.05..1,299,684.90 rows=219,972 width=80) (actual time=60,279.447..175,819.422 rows=221,467 loops=1)

  • Hash Cond: (r.document_kind_id = dk.id)
  • Buffers: shared hit=1,408 read=281,503 written=3,354
4. 115,036.232 175,231.072 ↓ 1.0 221,467 1

Bitmap Heap Scan on registry_item r (cost=575,627.27..1,298,485.49 rows=221,010 width=84) (actual time=60,277.210..175,231.072 rows=221,467 loops=1)

  • Recheck Cond: (version > 1)
  • Filter: (is_valid_item AND ((version_status)::text = 'A'::text))
  • Rows Removed by Filter: 10,975
  • Heap Blocks: exact=169,853
  • Buffers: shared hit=1,355 read=281,503 written=3,354
5. 60,194.840 60,194.840 ↓ 1.1 235,566 1

Bitmap Index Scan on emdr_registry_item_uni_base_id_and_version (cost=0.00..575,572.02 rows=224,300 width=0) (actual time=60,194.840..60,194.840 rows=235,566 loops=1)

  • Index Cond: (version > 1)
  • Buffers: shared hit=332 read=112,673 written=3,354
6. 0.094 1.218 ↑ 1.0 212 1

Hash (cost=55.13..55.13 rows=212 width=8) (actual time=1.218..1.218 rows=212 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=53
7. 1.124 1.124 ↑ 1.0 212 1

Seq Scan on document_kind dk (cost=0.00..55.13 rows=212 width=8) (actual time=0.061..1.124 rows=212 loops=1)

  • Filter: (nsi_id IS NOT NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=53
8.          

CTE all_prev_versions

9. 625.710 293,171.016 ↓ 1.1 209,302 1

WindowAgg (cost=3,430,222.91..3,436,311.27 rows=187,334 width=162) (actual time=292,517.360..293,171.016 rows=209,302 loops=1)

  • Buffers: shared hit=1,165,766 read=375,675 written=3,354
10. 2,412.399 292,545.306 ↓ 1.1 209,302 1

Sort (cost=3,430,222.91..3,430,691.25 rows=187,334 width=102) (actual time=292,517.132..292,545.306 rows=209,302 loops=1)

  • Sort Key: r_1.base_emdr_number
  • Sort Method: quicksort Memory: 35,578kB
  • Buffers: shared hit=1,165,766 read=375,675 written=3,354
11. 131.474 290,132.907 ↓ 1.1 209,302 1

Group (cost=3,408,665.21..3,413,816.90 rows=187,334 width=102) (actual time=289,965.384..290,132.907 rows=209,302 loops=1)

  • Group Key: r_1.reg_dt, ((r_prev.reg_dt)::date), r_1.ref_med_org_id, r_1.ref_department_id, r_1.region, r_1.vid, r_1.base_emdr_number, r_prev.version, r_1.version
  • Buffers: shared hit=1,165,763 read=375,675 written=3,354
12. 2,186.670 290,001.433 ↓ 1.1 209,302 1

Sort (cost=3,408,665.21..3,409,133.55 rows=187,334 width=102) (actual time=289,965.376..290,001.433 rows=209,302 loops=1)

  • Sort Key: r_1.reg_dt, ((r_prev.reg_dt)::date), r_1.ref_med_org_id, r_1.ref_department_id, r_1.region, r_1.vid, r_1.base_emdr_number, r_prev.version, r_1.version
  • Sort Method: quicksort Memory: 35,578kB
  • Buffers: shared hit=1,165,763 read=375,675 written=3,354
13. 905.542 287,814.763 ↓ 1.1 209,302 1

Nested Loop (cost=0.56..3,392,259.20 rows=187,334 width=102) (actual time=60,289.520..287,814.763 rows=209,302 loops=1)

  • Buffers: shared hit=1,165,751 read=375,675 written=3,354
14. 176,397.188 176,397.188 ↓ 1.0 221,467 1

CTE Scan on r r_1 (cost=0.00..4,399.44 rows=219,972 width=86) (actual time=60,279.455..176,397.188 rows=221,467 loops=1)

  • Buffers: shared hit=1,408 read=281,503 written=3,354
15. 110,512.033 110,512.033 ↑ 1.0 1 221,467

Index Scan using registry_item_base_emdr_number_idx on registry_item r_prev (cost=0.56..15.39 rows=1 width=31) (actual time=0.447..0.499 rows=1 loops=221,467)

  • Index Cond: ((base_emdr_number)::text = (r_1.base_emdr_number)::text)
  • Filter: ((reg_dt)::date < r_1.reg_dt)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1,164,343 read=94,172
16.          

CTE prev_version

17. 293,330.773 293,330.773 ↓ 214.9 201,325 1

CTE Scan on all_prev_versions (cost=0.00..4,215.01 rows=937 width=154) (actual time=292,517.365..293,330.773 rows=201,325 loops=1)

  • Filter: (prev_version = prev_version_max)
  • Rows Removed by Filter: 7,977
  • Buffers: shared hit=1,165,766 read=375,675 written=3,354
18. 165.552 293,655.848 ↓ 133.6 26,727 1

HashAggregate (cost=35.14..37.64 rows=200 width=118) (actual time=293,646.900..293,655.848 rows=26,727 loops=1)

  • Group Key: prev_version.dt_of_last_version, prev_version.date_of_prev_version, prev_version.ref_med_org_id, prev_version.ref_department_id, prev_version.region, prev_version.vid
  • Buffers: shared hit=1,165,766 read=375,675 written=3,354
19. 293,490.296 293,490.296 ↓ 214.9 201,325 1

CTE Scan on prev_version (cost=0.00..18.74 rows=937 width=114) (actual time=292,517.368..293,490.296 rows=201,325 loops=1)

  • Buffers: shared hit=1,165,766 read=375,675 written=3,354
20. 2.902 141.720 ↓ 133.6 26,727 1

Subquery Scan on *SELECT* 2 (cost=35.14..39.14 rows=200 width=118) (actual time=129.971..141.720 rows=26,727 loops=1)

21. 112.515 138.818 ↓ 133.6 26,727 1

HashAggregate (cost=35.14..37.14 rows=200 width=150) (actual time=129.969..138.818 rows=26,727 loops=1)

  • Group Key: prev_version_1.dt_of_last_version, prev_version_1.date_of_prev_version, prev_version_1.ref_med_org_id, prev_version_1.ref_department_id, prev_version_1.region, prev_version_1.vid
22. 26.303 26.303 ↓ 214.9 201,325 1

CTE Scan on prev_version prev_version_1 (cost=0.00..18.74 rows=937 width=114) (actual time=0.003..26.303 rows=201,325 loops=1)

Planning time : 2.555 ms
Execution time : 293,843.634 ms