explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fDYV

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 212,585.263 ↓ 50.0 50 1

Limit (cost=450,696.38..450,696.38 rows=1 width=966) (actual time=212,585.249..212,585.263 rows=50 loops=1)

2. 0.435 212,585.248 ↓ 50.0 50 1

Sort (cost=450,696.38..450,696.38 rows=1 width=966) (actual time=212,585.243..212,585.248 rows=50 loops=1)

  • Sort Key: pl.full_name
  • Sort Method: quicksort Memory: 66kB
3. 7.462 212,584.813 ↓ 79.0 79 1

Nested Loop Semi Join (cost=165,711.70..450,696.37 rows=1 width=966) (actual time=3,431.641..212,584.813 rows=79 loops=1)

  • Join Filter: (pd.place_id = places_get_id.id)
  • Rows Removed by Join Filter: 128034
4. 0.136 212,565.738 ↓ 79.0 79 1

Nested Loop Semi Join (cost=165,711.45..450,673.61 rows=1 width=920) (actual time=3,426.375..212,565.738 rows=79 loops=1)

5. 0.260 212,563.943 ↓ 79.0 79 1

Nested Loop Left Join (cost=165,711.20..450,660.84 rows=1 width=924) (actual time=3,425.788..212,563.943 rows=79 loops=1)

  • Join Filter: (pm.type_id = pmt.id)
6. 0.632 212,562.577 ↓ 79.0 79 1

Nested Loop Left Join (cost=165,711.06..450,652.67 rows=1 width=412) (actual time=3,425.762..212,562.577 rows=79 loops=1)

  • Join Filter: (par.id = pl.agency_region_id)
  • Rows Removed by Join Filter: 253
7. 2,355.255 212,561.392 ↓ 79.0 79 1

Nested Loop Left Join (cost=165,711.06..450,632.54 rows=1 width=270) (actual time=3,425.733..212,561.392 rows=79 loops=1)

  • Join Filter: (visit_files.promo_data_id = pd.id)
  • Rows Removed by Join Filter: 36279529
8. 0.227 10.600 ↓ 79.0 79 1

Nested Loop (cost=9.46..50.33 rows=1 width=206) (actual time=0.043..10.600 rows=79 loops=1)

9. 0.270 8.240 ↓ 79.0 79 1

Nested Loop (cost=9.17..42.03 rows=1 width=138) (actual time=0.039..8.240 rows=79 loops=1)

10. 0.348 6.548 ↓ 79.0 79 1

Nested Loop (cost=8.89..33.72 rows=1 width=125) (actual time=0.035..6.548 rows=79 loops=1)

11. 2.487 2.487 ↓ 79.0 79 1

Index Scan using centrally_agreed_promos_data_promo_id_place_id_user_id_date_key on centrally_agreed_promos_data pd (cost=0.43..15.36 rows=1 width=28) (actual time=0.009..2.487 rows=79 loops=1)

  • Index Cond: ((promo_id = 15659) AND (date >= '2020-03-18'::date) AND (date <= '2020-03-18'::date))
12. 0.079 3.713 ↑ 1.0 1 79

Nested Loop (cost=8.46..18.35 rows=1 width=105) (actual time=0.035..0.047 rows=1 loops=79)

13. 1.099 1.659 ↑ 1.0 1 79

Hash Join (cost=8.17..10.03 rows=1 width=28) (actual time=0.011..0.021 rows=1 loops=79)

  • Hash Cond: (c.id = pj.client_id)
14. 0.553 0.553 ↑ 1.0 67 79

Seq Scan on clients c (cost=0.00..1.67 rows=67 width=4) (actual time=0.003..0.007 rows=67 loops=79)

15. 0.002 0.007 ↑ 1.0 1 1

Hash (cost=8.16..8.16 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.005 0.005 ↑ 1.0 1 1

Index Scan using projects_pkey on projects pj (cost=0.14..8.16 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (id = 211)
17. 1.975 1.975 ↑ 1.0 1 79

Index Scan using centrally_agreed_promos_pkey on centrally_agreed_promos pm (cost=0.29..8.31 rows=1 width=81) (actual time=0.023..0.025 rows=1 loops=79)

  • Index Cond: (id = 15659)
  • Filter: ((project_id = 211) AND (type_id = 1))
18. 1.422 1.422 ↑ 1.0 1 79

Index Scan using users_pkey on users u (cost=0.28..8.30 rows=1 width=21) (actual time=0.018..0.018 rows=1 loops=79)

  • Index Cond: (id = pd.user_id)
19. 2.133 2.133 ↑ 1.0 1 79

Index Scan using places_pkey on places pl (cost=0.29..8.30 rows=1 width=76) (actual time=0.027..0.027 rows=1 loops=79)

  • Index Cond: (id = pd.place_id)
20. 204,801.338 210,195.537 ↓ 1.7 459,236 79

GroupAggregate (cost=165,701.60..444,385.49 rows=275,410 width=69) (actual time=10.375..2,660.703 rows=459,236 loops=79)

  • Group Key: visit_files.promo_data_id
21. 4,855.904 5,394.199 ↑ 1.0 512,744 79

Sort (cost=165,701.60..166,997.05 rows=518,178 width=78) (actual time=10.350..68.281 rows=512,744 loops=79)

  • Sort Key: visit_files.promo_data_id
  • Sort Method: external sort Disk: 38768kB
22. 538.295 538.295 ↑ 1.0 517,885 1

Seq Scan on visit_files (cost=0.00..93,492.01 rows=518,178 width=78) (actual time=0.025..538.295 rows=517,885 loops=1)

  • Filter: (promo_data_id IS NOT NULL)
  • Rows Removed by Filter: 1624255
23. 0.553 0.553 ↑ 112.5 4 79

Seq Scan on place_agency_regions par (cost=0.00..14.50 rows=450 width=150) (actual time=0.007..0.007 rows=4 loops=79)

24. 1.106 1.106 ↑ 1.0 1 79

Index Scan using centrally_agreed_promo_types_pkey on centrally_agreed_promo_types pmt (cost=0.14..8.16 rows=1 width=520) (actual time=0.014..0.014 rows=1 loops=79)

  • Index Cond: (id = 1)
25. 1.659 1.659 ↑ 5.0 1 79

Function Scan on projects_get_id (cost=0.25..12.75 rows=5 width=4) (actual time=0.021..0.021 rows=1 loops=79)

  • Filter: (id = 211)
  • Rows Removed by Filter: 248
26. 11.613 11.613 ↓ 1.6 1,622 79

Function Scan on places_get_id (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.065..0.147 rows=1,622 loops=79)

Planning time : 1.653 ms
Execution time : 212,589.474 ms