explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0NCi

Settings
# exclusive inclusive rows x rows loops node
1. 0.111 26,154.343 ↑ 1.0 50 1

Limit (cost=2,433,290.56..2,433,290.68 rows=50 width=1,678) (actual time=26,154.179..26,154.343 rows=50 loops=1)

  • Buffers: shared hit=96 read=295576, temp read=81141 written=82041
2.          

CTE infra_col_data

3. 161.310 2,310.870 ↓ 6.2 82,454 1

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

  • Buffers: shared hit=93 read=14308, temp read=2297 written=2295
4. 235.334 2,149.560 ↓ 6.4 84,268 1

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

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: external merge Disk: 11512kB
  • Buffers: shared hit=93 read=14308, temp read=2297 written=2295
5. 215.019 1,914.226 ↓ 6.4 84,268 1

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

  • Hash Cond: (po_infra_instan.id_infra_instan = po_proj_sub_infra.id_infra_instan)
  • Buffers: shared hit=93 read=14308, temp read=854 written=852
6. 350.810 709.270 ↓ 1.5 84,771 1

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

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
  • Buffers: shared hit=5 read=11226
7. 358.209 358.209 ↑ 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.017..358.209 rows=274,442 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
  • Buffers: shared hit=4 read=11210
8. 0.060 0.251 ↑ 1.0 53 1

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

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

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

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

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

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3073kB
  • Buffers: shared hit=88 read=3082, temp written=185
11. 357.032 894.536 ↓ 1.3 84,697 1

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

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
  • Buffers: shared hit=88 read=3082
12. 265.588 265.588 ↓ 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.012..265.588 rows=274,533 loops=1)

  • Buffers: shared hit=3 read=1746
13. 10.182 271.916 ↑ 2.1 9,532 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 666kB
  • Buffers: shared hit=85 read=1336
14. 93.860 261.734 ↑ 2.1 9,532 1

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

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
  • Buffers: shared hit=85 read=1336
15. 84.110 84.110 ↓ 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.011..84.110 rows=85,261 loops=1)

  • Buffers: shared hit=1 read=931
16. 12.826 83.764 ↓ 1.8 12,498 1

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

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 617kB
  • Buffers: shared hit=84 read=405
17. 41.439 70.938 ↓ 1.8 12,498 1

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

  • Hash Cond: (po_project.id_organization = org.id_organization)
  • Buffers: shared hit=84 read=405
18. 29.295 29.295 ↓ 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.295 rows=29,343 loops=1)

  • Buffers: shared hit=2 read=403
19. 0.075 0.204 ↑ 1.0 70 1

Hash (cost=26.81..26.81 rows=70 width=4) (actual time=0.203..0.204 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=82 read=2
20. 0.129 0.129 ↑ 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.012..0.129 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=82 read=2
21. 2,975.763 26,154.232 ↑ 8,759.2 50 1

Sort (cost=2,405,175.14..2,406,270.04 rows=437,958 width=1,678) (actual time=26,154.175..26,154.232 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 41kB
  • Buffers: shared hit=96 read=295576, temp read=81141 written=82041
22. 3,302.722 23,178.469 ↓ 4.9 2,151,577 1

Hash Join (cost=2,081,284.69..2,390,626.49 rows=437,958 width=1,678) (actual time=19,439.791..23,178.469 rows=2,151,577 loops=1)

  • Hash Cond: (infra_col_data.id_infra_instan = _req.id_infra_instan)
  • Buffers: shared hit=96 read=295576, temp read=81141 written=82041
23. 2,493.490 2,493.490 ↓ 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,050.694..2,493.490 rows=82,454 loops=1)

  • Buffers: shared hit=93 read=14308, temp read=2297 written=3707
24. 6,684.580 17,382.257 ↑ 1.0 4,872,824 1

Hash (cost=1,937,863.02..1,937,863.02 rows=4,928,293 width=106) (actual time=17,382.255..17,382.257 rows=4,872,824 loops=1)

  • Buckets: 32768 Batches: 256 Memory Usage: 3019kB
  • Buffers: shared read=281268, temp written=76502
25. 10,206.735 10,697.677 ↑ 1.0 4,872,824 1

Bitmap Heap Scan on po_import_request _req (cost=105,205.90..1,937,863.02 rows=4,928,293 width=106) (actual time=496.375..10,697.677 rows=4,872,824 loops=1)

  • Recheck Cond: (((ts_status_last_updated_at)::date >= '2019-03-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-04-01'::date))
  • Rows Removed by Index Recheck: 6266364
  • Filter: ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,5}'::integer[]))
  • Rows Removed by Filter: 142818
  • Heap Blocks: exact=27283 lossy=240277
  • Buffers: shared read=281268
26. 490.942 490.942 ↓ 1.0 5,015,642 1

Bitmap Index Scan on idx_po_import_request_ts_status_last_updated_at_extraction (cost=0.00..103,973.83 rows=4,946,926 width=0) (actual time=490.940..490.942 rows=5,015,642 loops=1)

  • Index Cond: (((ts_status_last_updated_at)::date >= '2019-03-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-04-01'::date))
  • Buffers: shared read=13708
Planning time : 1.463 ms
Execution time : 26,159.950 ms