explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lo0x

Settings
# exclusive inclusive rows x rows loops node
1. 11.690 9,226.574 ↓ 8.0 18,422 1

GroupAggregate (cost=507,889.71..507,987.93 rows=2,311 width=87) (actual time=9,214.042..9,226.574 rows=18,422 loops=1)

  • Group Key: stats_adrevenue.date, stats_adrevenue.country, core_adrevenue_app_map.app_id, core_app.customer_id, core_connectedchannel.channel_id, stats_adrevenue.ad_unit_map_id
2. 34.606 9,214.884 ↓ 8.0 18,480 1

Sort (cost=507,889.71..507,895.49 rows=2,311 width=43) (actual time=9,214.027..9,214.884 rows=18,480 loops=1)

  • Sort Key: stats_adrevenue.date, stats_adrevenue.country, core_app.customer_id, core_connectedchannel.channel_id, stats_adrevenue.ad_unit_map_id
  • Sort Method: quicksort Memory: 2,245kB
3. 6.059 9,180.278 ↓ 8.0 18,480 1

Hash Left Join (cost=83,882.54..507,760.59 rows=2,311 width=43) (actual time=2,715.692..9,180.278 rows=18,480 loops=1)

  • Hash Cond: ((core_app.id = sq0.app_id) AND (stats_adrevenue.date = sq0.date) AND ((stats_adrevenue.country)::text = (sq0.country)::text))
4. 11.059 8,300.032 ↓ 8.0 18,480 1

Nested Loop (cost=16,495.81..440,355.67 rows=2,311 width=43) (actual time=1,841.484..8,300.032 rows=18,480 loops=1)

5. 2.263 8,270.493 ↓ 8.0 18,480 1

Nested Loop (cost=16,495.52..439,637.83 rows=2,311 width=43) (actual time=1,840.688..8,270.493 rows=18,480 loops=1)

6. 0.045 0.655 ↑ 1.0 25 1

Nested Loop (cost=0.56..45.33 rows=26 width=16) (actual time=0.189..0.655 rows=25 loops=1)

7. 0.026 0.026 ↑ 1.0 1 1

Index Scan using core_app_pkey on core_app (cost=0.28..4.30 rows=1 width=8) (actual time=0.025..0.026 rows=1 loops=1)

  • Index Cond: (id = 4,169)
8. 0.584 0.584 ↑ 1.0 25 1

Index Scan using core_adrevenue_app_map_app_id_a0995552 on core_adrevenue_app_map (cost=0.28..40.77 rows=26 width=8) (actual time=0.162..0.584 rows=25 loops=1)

  • Index Cond: (app_id = 4,169)
9. 136.750 8,267.575 ↓ 3.6 739 25

Bitmap Heap Scan on stats_adrevenue (cost=16,494.96..16,905.36 rows=204 width=35) (actual time=328.381..330.703 rows=739 loops=25)

  • Recheck Cond: ((app_map_id = core_adrevenue_app_map.id) AND (date >= '2020-07-26'::date) AND (date <= '2020-07-27'::date))
  • Heap Blocks: exact=5,496
10. 31.450 8,130.825 ↓ 0.0 0 25

BitmapAnd (cost=16,494.96..16,494.96 rows=204 width=0) (actual time=325.233..325.233 rows=0 loops=25)

11. 1,987.800 1,987.800 ↓ 1.3 107,866 25

Bitmap Index Scan on stats_adrevenue_app_map_id_54a34d1e (cost=0.00..2,217.24 rows=84,500 width=0) (actual time=79.512..79.512 rows=107,866 loops=25)

  • Index Cond: (app_map_id = core_adrevenue_app_map.id)
12. 6,111.575 6,111.575 ↓ 7.0 4,168,735 25

Bitmap Index Scan on stats_adrevenue_date_9c7168e0 (cost=0.00..14,129.25 rows=592,668 width=0) (actual time=244.463..244.463 rows=4,168,735 loops=25)

  • Index Cond: ((date >= '2020-07-26'::date) AND (date <= '2020-07-27'::date))
13. 18.480 18.480 ↑ 1.0 1 18,480

Index Scan using core_connectedchannel_pkey on core_connectedchannel (cost=0.29..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=18,480)

  • Index Cond: (id = stats_adrevenue.connected_channel_id)
14. 0.105 874.187 ↑ 3.0 474 1

Hash (cost=67,362.00..67,362.00 rows=1,413 width=14) (actual time=874.187..874.187 rows=474 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 39kB
15. 0.043 874.082 ↑ 3.0 474 1

Subquery Scan on sq0 (cost=67,215.44..67,362.00 rows=1,413 width=14) (actual time=871.058..874.082 rows=474 loops=1)

16. 1.969 874.039 ↑ 3.0 474 1

GroupAggregate (cost=67,215.44..67,347.87 rows=1,413 width=14) (actual time=871.057..874.039 rows=474 loops=1)

  • Group Key: stats_general_regular.date, stats_general_regular.app_id, stats_general_regular.country
17. 11.081 872.070 ↓ 1.5 13,451 1

Sort (cost=67,215.44..67,238.40 rows=9,181 width=18) (actual time=871.042..872.070 rows=13,451 loops=1)

  • Sort Key: stats_general_regular.date, stats_general_regular.country
  • Sort Method: quicksort Memory: 1,435kB
18. 0.780 860.989 ↓ 1.5 13,451 1

Append (cost=48,207.22..66,611.13 rows=9,181 width=18) (actual time=812.386..860.989 rows=13,451 loops=1)

19. 66.051 860.209 ↓ 1.5 13,451 1

Bitmap Heap Scan on stats_general_regular (cost=48,207.22..66,565.23 rows=9,181 width=18) (actual time=812.385..860.209 rows=13,451 loops=1)

  • Recheck Cond: ((customer_id = 857) AND (date >= '2020-07-26'::date) AND (date <= '2020-07-27'::date) AND (app_id = 4,169))
  • Heap Blocks: exact=6,552
20. 54.963 794.158 ↓ 0.0 0 1

BitmapAnd (cost=48,207.22..48,207.22 rows=9,181 width=0) (actual time=794.158..794.158 rows=0 loops=1)

21. 371.639 371.639 ↓ 2.0 1,711,112 1

Bitmap Index Scan on stats_general_regular_customer_date_idx (cost=0.00..18,637.05 rows=868,838 width=0) (actual time=371.639..371.639 rows=1,711,112 loops=1)

  • Index Cond: ((customer_id = 857) AND (date >= '2020-07-26'::date) AND (date <= '2020-07-27'::date))
22. 367.556 367.556 ↓ 1.1 1,641,453 1

Bitmap Index Scan on stats_general_regular_app_idx (cost=0.00..29,565.34 rows=1,521,969 width=0) (actual time=367.556..367.556 rows=1,641,453 loops=1)

  • Index Cond: (app_id = 4,169)
Planning time : 2.119 ms
Execution time : 9,233.021 ms