explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t5sq

Settings
# exclusive inclusive rows x rows loops node
1. 3.275 809.493 ↓ 50.0 50 1

Nested Loop (cost=2,091.45..2,099.53 rows=1 width=1,979) (actual time=637.913..809.493 rows=50 loops=1)

  • Join Filter: (sy_temp_po_import_non_complete.id_infra_instan = infra_col.id_infra_instan)
  • Rows Removed by Join Filter: 2400
2.          

CTE ids

3. 9.000 622.211 ↓ 50.0 50 1

Limit (cost=2,069.90..2,069.90 rows=1 width=52) (actual time=621.990..622.211 rows=50 loops=1)

4. 247.260 613.211 ↓ 48.6 10,050 1

Sort (cost=2,069.38..2,069.90 rows=207 width=52) (actual time=603.054..613.211 rows=10,050 loops=1)

  • Sort Key: sy_temp_po_import_non_complete_1.ts_status_last_updated_at
  • Sort Method: top-N heapsort Memory: 1999kB
5. 365.951 365.951 ↓ 741.0 153,377 1

Index Scan using idx_sy_temp_po_import_non_complete_nu_extraction_status_ts_last on sy_temp_po_import_non_complete sy_temp_po_import_non_complete_1 (cost=0.43..2,061.42 rows=207 width=52) (actual time=1.399..365.951 rows=153,377 loops=1)

  • Index Cond: ((id_enterprise = 83) AND ((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[])))
6.          

CTE infra_col

7. 0.159 15.529 ↓ 49.0 49 1

Unique (cost=20.99..21.00 rows=1 width=178) (actual time=15.298..15.529 rows=49 loops=1)

8. 0.218 15.370 ↓ 50.0 50 1

Sort (cost=20.99..20.99 rows=1 width=178) (actual time=15.295..15.370 rows=50 loops=1)

  • Sort Key: ids_1.id_infra_instan
  • Sort Method: quicksort Memory: 32kB
9. 0.304 15.152 ↓ 50.0 50 1

Nested Loop (cost=1.84..20.98 rows=1 width=178) (actual time=1.590..15.152 rows=50 loops=1)

10. 0.519 14.348 ↓ 50.0 50 1

Nested Loop (cost=1.57..20.68 rows=1 width=178) (actual time=1.492..14.348 rows=50 loops=1)

11. 0.269 13.279 ↓ 50.0 50 1

Nested Loop (cost=1.42..9.63 rows=1 width=178) (actual time=1.440..13.279 rows=50 loops=1)

12. 0.342 11.910 ↓ 50.0 50 1

Nested Loop (cost=1.14..9.29 rows=1 width=174) (actual time=1.020..11.910 rows=50 loops=1)

13. 0.290 11.168 ↓ 50.0 50 1

Nested Loop (cost=0.84..8.96 rows=1 width=170) (actual time=0.954..11.168 rows=50 loops=1)

14. 0.328 3.178 ↓ 50.0 50 1

Nested Loop (cost=0.42..8.47 rows=1 width=170) (actual time=0.489..3.178 rows=50 loops=1)

15. 0.400 0.400 ↓ 50.0 50 1

CTE Scan on ids ids_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.400 rows=50 loops=1)

16. 2.450 2.450 ↑ 1.0 1 50

Index Scan using po_infra_instan_pkey on po_infra_instan (cost=0.42..8.44 rows=1 width=162) (actual time=0.047..0.049 rows=1 loops=50)

  • Index Cond: (id_infra_instan = ids_1.id_infra_instan)
  • Filter: (NOT bl_marked_for_deletion)
17. 7.700 7.700 ↑ 1.0 1 50

Index Scan using index_po_proj_sub_infra_id_infra_instan on po_proj_sub_infra (cost=0.42..0.48 rows=1 width=8) (actual time=0.152..0.154 rows=1 loops=50)

  • Index Cond: (id_infra_instan = po_infra_instan.id_infra_instan)
18. 0.400 0.400 ↑ 1.0 1 50

Index Scan using po_project_sub_id_project_sub_key on po_project_sub (cost=0.29..0.33 rows=1 width=8) (actual time=0.006..0.008 rows=1 loops=50)

  • Index Cond: (id_project_sub = po_proj_sub_infra.id_project_sub)
19. 1.100 1.100 ↑ 1.0 1 50

Index Scan using po_project_pkey on po_project (cost=0.29..0.33 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=50)

  • Index Cond: (id_project = po_project_sub.id_project)
20. 0.550 0.550 ↑ 1.0 1 50

Index Only Scan using po_organization_pkey on po_organization org (cost=0.15..11.04 rows=1 width=4) (actual time=0.006..0.011 rows=1 loops=50)

  • Index Cond: ((id_organization = po_project.id_organization) AND (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: 50
21. 0.500 0.500 ↑ 1.0 1 50

Index Scan using po_connector_details_pkey on po_connector_details (cost=0.27..0.29 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=50)

  • Index Cond: (id_connector_details = po_infra_instan.id_connector_details)
  • Filter: (id_enterprise = 83)
22. 0.390 787.568 ↓ 50.0 50 1

Nested Loop (cost=0.56..8.60 rows=1 width=402) (actual time=622.571..787.568 rows=50 loops=1)

23. 622.078 622.078 ↓ 50.0 50 1

CTE Scan on ids (cost=0.00..0.02 rows=1 width=98) (actual time=621.994..622.078 rows=50 loops=1)

24. 165.100 165.100 ↑ 1.0 1 50

Index Scan using sy_temp_po_import_non_complete_id_import_request_pk on sy_temp_po_import_non_complete (cost=0.56..8.57 rows=1 width=394) (actual time=3.300..3.302 rows=1 loops=50)

  • Index Cond: ((id_import_request)::text = (ids.id_import_request)::text)
25. 18.650 18.650 ↓ 49.0 49 50

CTE Scan on infra_col (cost=0.00..0.02 rows=1 width=1,585) (actual time=0.308..0.373 rows=49 loops=50)

Planning time : 4.020 ms
Execution time : 810.419 ms