explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2k3K

Settings
# exclusive inclusive rows x rows loops node
1. 0.106 4,790.923 ↑ 1.0 50 1

Limit (cost=360,039.93..360,040.06 rows=50 width=1,678) (actual time=4,790.769..4,790.923 rows=50 loops=1)

  • Buffers: shared hit=1,729,740 read=82,459 written=72, temp read=2,297 written=3,707
2.          

CTE infra_col_data

3. 167.439 2,289.177 ↓ 6.2 82,454 1

Unique (cost=28,049.08..28,115.42 rows=13,267 width=174) (actual time=2,013.873..2,289.177 rows=82,454 loops=1)

  • Buffers: shared hit=9,444 read=4,957, temp read=2,297 written=2,295
4. 242.389 2,121.738 ↓ 6.4 84,268 1

Sort (cost=28,049.08..28,082.25 rows=13,267 width=174) (actual time=2,013.870..2,121.738 rows=84,268 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: external merge Disk: 11,512kB
  • Buffers: shared hit=9,444 read=4,957, temp read=2,297 written=2,295
5. 210.514 1,879.349 ↓ 6.4 84,268 1

Hash Join (cost=10,514.34..27,140.59 rows=13,267 width=174) (actual time=979.950..1,879.349 rows=84,268 loops=1)

  • Hash Cond: (po_infra_instan.id_infra_instan = po_proj_sub_infra.id_infra_instan)
  • Buffers: shared hit=9,444 read=4,957, temp read=854 written=852
6. 346.380 689.364 ↓ 1.5 84,771 1

Hash Join (cost=20.84..15,583.75 rows=57,272 width=162) (actual time=0.270..689.364 rows=84,771 loops=1)

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
  • Buffers: shared hit=7,715 read=3,516
7. 342.733 342.733 ↑ 1.0 274,442 1

Seq Scan on po_infra_instan (cost=0.00..13,960.92 rows=274,472 width=162) (actual time=0.010..342.733 rows=274,442 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
  • Buffers: shared hit=7,714 read=3,500
8. 0.058 0.251 ↑ 1.0 53 1

Hash (cost=20.18..20.18 rows=53 width=4) (actual time=0.251..0.251 rows=53 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1 read=16
9. 0.193 0.193 ↑ 1.0 53 1

Seq Scan on po_connector_details (cost=0.00..20.18 rows=53 width=4) (actual time=0.013..0.193 rows=53 loops=1)

  • Filter: (id_enterprise = 83)
  • Rows Removed by Filter: 202
  • Buffers: shared hit=1 read=16
10. 93.301 979.471 ↓ 1.3 84,697 1

Hash (cost=9,698.11..9,698.11 rows=63,631 width=16) (actual time=979.471..979.471 rows=84,697 loops=1)

  • Buckets: 131,072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3,073kB
  • Buffers: shared hit=1,729 read=1,441, temp written=185
11. 354.586 886.170 ↓ 1.3 84,697 1

Hash Join (cost=3,538.13..9,698.11 rows=63,631 width=16) (actual time=272.559..886.170 rows=84,697 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
  • Buffers: shared hit=1,729 read=1,441
12. 259.275 259.275 ↓ 1.0 274,533 1

Seq Scan on po_proj_sub_infra (cost=0.00..4,494.22 rows=274,522 width=8) (actual time=0.007..259.275 rows=274,533 loops=1)

  • Buffers: shared hit=1,641 read=108
13. 10.203 272.309 ↑ 2.1 9,532 1

Hash (cost=3,291.11..3,291.11 rows=19,762 width=12) (actual time=272.308..272.309 rows=9,532 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 666kB
  • Buffers: shared hit=88 read=1,333
14. 93.787 262.106 ↑ 2.1 9,532 1

Hash Join (cost=989.16..3,291.11 rows=19,762 width=12) (actual time=83.989..262.106 rows=9,532 loops=1)

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
  • Buffers: shared hit=88 read=1,333
15. 84.360 84.360 ↓ 1.0 85,261 1

Seq Scan on po_project_sub (cost=0.00..1,784.60 rows=85,260 width=8) (actual time=0.013..84.360 rows=85,261 loops=1)

  • Buffers: shared hit=2 read=930
16. 12.849 83.959 ↓ 1.8 12,498 1

Hash (cost=904.15..904.15 rows=6,801 width=8) (actual time=83.958..83.959 rows=12,498 loops=1)

  • Buckets: 16,384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 617kB
  • Buffers: shared hit=86 read=403
17. 41.484 71.110 ↓ 1.8 12,498 1

Hash Join (cost=27.69..904.15 rows=6,801 width=8) (actual time=0.801..71.110 rows=12,498 loops=1)

  • Hash Cond: (po_project.id_organization = org.id_organization)
  • Buffers: shared hit=86 read=403
18. 29.398 29.398 ↓ 1.0 29,343 1

Seq Scan on po_project (cost=0.00..698.42 rows=29,342 width=8) (actual time=0.011..29.398 rows=29,343 loops=1)

  • Buffers: shared hit=3 read=402
19. 0.075 0.228 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.227..0.228 rows=70 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=83 read=1
20. 0.153 0.153 ↑ 1.0 70 1

Index Only Scan using po_organization_pkey on po_organization org (cost=0.15..26.81 rows=70 width=4) (actual time=0.006..0.153 rows=70 loops=1)

  • Index Cond: (id_organization = ANY ('{126,133,190,191,192,193,194,195,196,197,198,199,203,204,211,272,287,288,289,290,291,292,293,331,333,337,338,339,340,341,342,347,349,350,351,352,353,354,355,356,361,362,363,366,367,368,369,373,374,375,376,377,378,379,380,381,382,383,384,385,386,396,397,398,399,400,401,402,403,424}'::integer[]))
  • Heap Fetches: 70
  • Buffers: shared hit=83 read=1
21. 224.952 4,790.817 ↑ 386.3 50 1

Sort (cost=331,924.51..331,972.81 rows=19,317 width=1,678) (actual time=4,790.764..4,790.817 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 50kB
  • Buffers: shared hit=1,729,740 read=82,459 written=72, temp read=2,297 written=3,707
22. 524.496 4,565.865 ↓ 7.9 153,377 1

Nested Loop (cost=0.57..331,282.82 rows=19,317 width=1,678) (actual time=2,028.860..4,565.865 rows=153,377 loops=1)

  • Buffers: shared hit=1,729,740 read=82,459 written=72, temp read=2,297 written=3,707
23. 2,474.743 2,474.743 ↓ 6.2 82,454 1

CTE Scan on infra_col_data (cost=0.00..265.34 rows=13,267 width=1,576) (actual time=2,013.877..2,474.743 rows=82,454 loops=1)

  • Buffers: shared hit=9,444 read=4,957, temp read=2,297 written=3,707
24. 1,566.626 1,566.626 ↓ 2.0 2 82,454

Index Scan using idx_po_import_request_id_infra_extraction_ts_status_last_update on po_import_request _req (cost=0.57..24.94 rows=1 width=106) (actual time=0.015..0.019 rows=2 loops=82,454)

  • Index Cond: ((id_infra_instan = infra_col_data.id_infra_instan) AND ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])) AND ((ts_status_last_updated_at)::date >= '2019-03-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-04-01'::date))
  • Buffers: shared hit=1,720,296 read=77,502 written=72
Planning time : 1.867 ms
Execution time : 4,796.667 ms