explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qoUx

Settings
# exclusive inclusive rows x rows loops node
1. 0.108 5,304.781 ↑ 1.0 50 1

Limit (cost=122,475.47..122,475.60 rows=50 width=1,678) (actual time=5,304.623..5,304.781 rows=50 loops=1)

  • Buffers: shared hit=46,781 read=71,294, temp read=9,428 written=11,347
2.          

CTE infra_col_data

3. 162.340 2,300.907 ↓ 6.2 82,454 1

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

  • Buffers: shared hit=3,222 read=11,179, temp read=2,296 written=2,294
4. 235.866 2,138.567 ↓ 6.4 84,268 1

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

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: external merge Disk: 11,504kB
  • Buffers: shared hit=3,222 read=11,179, temp read=2,296 written=2,294
5. 211.490 1,902.701 ↓ 6.4 84,268 1

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

  • Hash Cond: (po_infra_instan.id_infra_instan = po_proj_sub_infra.id_infra_instan)
  • Buffers: shared hit=3,222 read=11,179, temp read=854 written=852
6. 347.316 702.342 ↓ 1.5 84,771 1

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

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
  • Buffers: shared hit=52 read=11,179
7. 354.793 354.793 ↑ 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.023..354.793 rows=274,442 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
  • Buffers: shared hit=35 read=11,179
8. 0.066 0.233 ↑ 1.0 53 1

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

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

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

  • Filter: (id_enterprise = 83)
  • Rows Removed by Filter: 202
  • Buffers: shared hit=17
10. 95.822 988.869 ↓ 1.3 84,697 1

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

  • Buckets: 131,072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3,073kB
  • Buffers: shared hit=3,170, temp written=185
11. 355.136 893.047 ↓ 1.3 84,697 1

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

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
  • Buffers: shared hit=3,170
12. 263.546 263.546 ↓ 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.011..263.546 rows=274,533 loops=1)

  • Buffers: shared hit=1,749
13. 10.564 274.365 ↑ 2.1 9,532 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 666kB
  • Buffers: shared hit=1,421
14. 95.321 263.801 ↑ 2.1 9,532 1

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

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

  • Buffers: shared hit=932
16. 13.187 84.154 ↓ 1.8 12,498 1

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

  • Buckets: 16,384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 617kB
  • Buffers: shared hit=489
17. 41.788 70.967 ↓ 1.8 12,498 1

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

  • Hash Cond: (po_project.id_organization = org.id_organization)
  • Buffers: shared hit=489
18. 28.950 28.950 ↓ 1.0 29,343 1

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

  • Buffers: shared hit=405
19. 0.085 0.229 ↑ 1.0 70 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=84
20. 0.144 0.144 ↑ 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.019..0.144 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=84
21. 218.174 5,304.673 ↑ 17.6 50 1

Sort (cost=94,360.06..94,362.26 rows=882 width=1,678) (actual time=5,304.619..5,304.673 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 50kB
  • Buffers: shared hit=46,781 read=71,294, temp read=9,428 written=11,347
22. 397.825 5,086.499 ↓ 173.9 153,377 1

Merge Join (cost=94,206.42..94,330.76 rows=882 width=1,678) (actual time=4,429.291..5,086.499 rows=153,377 loops=1)

  • Merge Cond: (infra_col_data.id_infra_instan = _req.id_infra_instan)
  • Buffers: shared hit=46,778 read=71,294, temp read=9,428 written=11,347
23. 203.292 2,689.894 ↓ 3.8 50,025 1

Sort (cost=10,245.83..10,279.00 rows=13,267 width=1,576) (actual time=2,634.151..2,689.894 rows=50,025 loops=1)

  • Sort Key: infra_col_data.id_infra_instan
  • Sort Method: external sort Disk: 10,880kB
  • Buffers: shared hit=3,225 read=11,179, temp read=3,147 written=5,066
24. 2,486.602 2,486.602 ↓ 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,039.689..2,486.602 rows=82,454 loops=1)

  • Buffers: shared hit=3,222 read=11,179, temp read=2,296 written=3,706
25. 581.198 1,998.780 ↓ 20.2 198,759 1

Sort (cost=83,960.58..83,985.17 rows=9,837 width=106) (actual time=1,786.174..1,998.780 rows=198,759 loops=1)

  • Sort Key: _req.id_infra_instan
  • Sort Method: external sort Disk: 25,104kB
  • Buffers: shared hit=43,553 read=60,115, temp read=6,281 written=6,281
26. 482.994 1,417.582 ↓ 20.2 198,759 1

Bitmap Heap Scan on po_import_request _req (cost=46,030.98..83,308.19 rows=9,837 width=106) (actual time=942.246..1,417.582 rows=198,759 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) AND ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])))
  • Heap Blocks: exact=36,159
  • Buffers: shared hit=43,546 read=60,115
27. 934.588 934.588 ↓ 20.2 198,759 1

Bitmap Index Scan on idx_po_import_request_ts_status_last_updated_at_extraction (cost=0.00..46,028.53 rows=9,837 width=0) (actual time=934.587..934.588 rows=198,759 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) AND ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])))
  • Buffers: shared hit=43,546 read=23,956
Planning time : 4.182 ms
Execution time : 5,320.810 ms