explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Hz

Settings
# exclusive inclusive rows x rows loops node
1. 0.106 6,068.448 ↑ 1.0 50 1

Limit (cost=76,694.54..76,694.66 rows=50 width=1,678) (actual time=6,068.293..6,068.448 rows=50 loops=1)

2.          

CTE infra_col_data

3. 186.981 2,601.275 ↓ 6.2 82,453 1

Unique (cost=28,037.38..28,103.44 rows=13,211 width=174) (actual time=2,298.012..2,601.275 rows=82,453 loops=1)

4. 285.240 2,414.294 ↓ 6.4 84,267 1

Sort (cost=28,037.38..28,070.41 rows=13,211 width=174) (actual time=2,298.009..2,414.294 rows=84,267 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: external merge Disk: 11520kB
5. 258.517 2,129.054 ↓ 6.4 84,267 1

Hash Join (cost=10,514.35..27,133.13 rows=13,211 width=174) (actual time=1,041.641..2,129.054 rows=84,267 loops=1)

  • Hash Cond: (po_infra_instan.id_infra_instan = po_proj_sub_infra.id_infra_instan)
6. 405.828 829.441 ↓ 1.5 84,770 1

Hash Join (cost=20.85..15,580.81 rows=57,028 width=162) (actual time=0.309..829.441 rows=84,770 loops=1)

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
7. 423.363 423.363 ↓ 1.0 274,441 1

Seq Scan on po_infra_instan (cost=0.00..13,960.75 rows=274,382 width=162) (actual time=0.015..423.363 rows=274,441 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
8. 0.063 0.250 ↑ 1.0 53 1

Hash (cost=20.19..20.19 rows=53 width=4) (actual time=0.249..0.250 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.187 0.187 ↑ 1.0 53 1

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

  • Filter: (id_enterprise = 83)
  • Rows Removed by Filter: 202
10. 98.274 1,041.096 ↓ 1.3 84,696 1

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

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3073kB
11. 375.218 942.822 ↓ 1.3 84,696 1

Hash Join (cost=3,538.13..9,698.11 rows=63,631 width=16) (actual time=286.741..942.822 rows=84,696 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
12. 281.130 281.130 ↓ 1.0 274,532 1

Seq Scan on po_proj_sub_infra (cost=0.00..4,494.22 rows=274,522 width=8) (actual time=0.012..281.130 rows=274,532 loops=1)

13. 10.604 286.474 ↑ 2.1 9,532 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 666kB
14. 98.843 275.870 ↑ 2.1 9,532 1

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

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
15. 88.765 88.765 ↓ 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.012..88.765 rows=85,261 loops=1)

16. 13.350 88.262 ↓ 1.8 12,498 1

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

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 617kB
17. 43.646 74.912 ↓ 1.8 12,498 1

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

  • Hash Cond: (po_project.id_organization = org.id_organization)
18. 31.019 31.019 ↓ 1.0 29,343 1

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

19. 0.080 0.247 ↑ 1.0 70 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.167 0.167 ↑ 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.167 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
21. 1.932 6,068.342 ↑ 17.0 50 1

Sort (cost=48,591.10..48,593.22 rows=850 width=1,678) (actual time=6,068.290..6,068.342 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 39kB
22. 238.153 6,066.410 ↓ 1.3 1,066 1

Merge Join (cost=48,439.12..48,562.86 rows=850 width=1,678) (actual time=5,607.075..6,066.410 rows=1,066 loops=1)

  • Merge Cond: (infra_col_data.id_infra_instan = _req.id_infra_instan)
23. 230.085 3,051.858 ↓ 2.7 35,321 1

Sort (cost=10,201.98..10,235.00 rows=13,211 width=1,576) (actual time=3,012.917..3,051.858 rows=35,321 loops=1)

  • Sort Key: infra_col_data.id_infra_instan
  • Sort Method: external sort Disk: 10880kB
24. 2,821.773 2,821.773 ↓ 6.2 82,453 1

CTE Scan on infra_col_data (cost=0.00..264.22 rows=13,211 width=1,576) (actual time=2,298.018..2,821.773 rows=82,453 loops=1)

25. 849.291 2,776.399 ↓ 22.3 219,157 1

Sort (cost=38,237.14..38,261.74 rows=9,837 width=106) (actual time=2,539.847..2,776.399 rows=219,157 loops=1)

  • Sort Key: _req.id_infra_instan
  • Sort Method: external sort Disk: 35296kB
26. 1,867.264 1,927.108 ↓ 22.3 219,157 1

Bitmap Heap Scan on po_import_request_exact_copy _req (cost=307.55..37,584.75 rows=9,837 width=106) (actual time=66.744..1,927.108 rows=219,157 loops=1)

  • Recheck Cond: (((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])) AND ((ts_status_last_updated_at)::date >= '2018-01-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
  • Rows Removed by Index Recheck: 2392948
  • Heap Blocks: exact=25952 lossy=54651
27. 59.844 59.844 ↓ 22.3 219,157 1

Bitmap Index Scan on idx_po_import_request_exact_copy_extraction_ts_update_id_infra (cost=0.00..305.09 rows=9,837 width=0) (actual time=59.843..59.844 rows=219,157 loops=1)

  • Index Cond: (((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[])) AND ((ts_status_last_updated_at)::date >= '2018-01-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
Planning time : 1.513 ms
Execution time : 6,088.185 ms