explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wEr9

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 10,151.329 ↑ 1.0 5 1

Limit (cost=29,055.81..29,055.82 rows=5 width=1,596) (actual time=10,151.325..10,151.329 rows=5 loops=1)

2. 240.302 10,151.324 ↑ 8.8 5 1

Sort (cost=29,055.81..29,055.92 rows=44 width=1,596) (actual time=10,151.324..10,151.324 rows=5 loops=1)

  • Sort Key: c.customer_name
  • Sort Method: top-N heapsort Memory: 25kB
3. 104.576 9,911.022 ↓ 13,634.5 599,918 1

Nested Loop (cost=697.13..29,055.08 rows=44 width=1,596) (actual time=1,092.083..9,911.022 rows=599,918 loops=1)

  • Join Filter: (c.customer_type_id = ct.customer_type_internal_id)
  • Rows Removed by Join Filter: 1199836
4. 0.025 0.025 ↓ 3.0 3 1

Seq Scan on customer_type ct (cost=0.00..1.05 rows=1 width=524) (actual time=0.009..0.025 rows=3 loops=1)

  • Filter: ((start_date <= '2020-01-01'::date) AND (end_date > '2020-01-01'::date) AND (request_id = 0))
5. 5,463.975 9,806.421 ↓ 4,510.7 599,918 3

Hash Join (cost=697.13..29,052.37 rows=133 width=1,088) (actual time=1,132.392..3,268.807 rows=599,918 loops=3)

  • Hash Cond: (c.customer_internal_id = ca.customer_internal_id)
6. 1,036.992 1,036.992 ↑ 1.0 1,048,575 3

Seq Scan on customer c (cost=0.00..24,421.75 rows=1,048,575 width=51) (actual time=0.049..345.664 rows=1,048,575 loops=3)

7. 1,205.064 3,305.454 ↓ 4,510.7 599,920 3

Hash (cost=695.47..695.47 rows=133 width=1,045) (actual time=1,101.818..1,101.818 rows=599,920 loops=3)

  • Buckets: 65536 (originally 1024) Batches: 16 (originally 1) Memory Usage: 3585kB
8. 326.580 2,100.390 ↓ 4,510.7 599,920 3

Nested Loop (cost=6.04..695.47 rows=133 width=1,045) (actual time=0.421..700.130 rows=599,920 loops=3)

9. 6.534 6.534 ↓ 7,013.0 7,013 3

Function Scan on getgeographiesinspan g (cost=0.25..17.75 rows=1 width=1,040) (actual time=0.349..2.178 rows=7,013 loops=3)

  • Filter: ((start_date <= '2020-01-01'::date) AND (end_date > '2020-01-01'::date) AND (request_id = 0))
10. 1,598.964 1,767.276 ↑ 1.5 86 21,039

Bitmap Heap Scan on customer_alignment ca (cost=5.79..676.39 rows=133 width=21) (actual time=0.032..0.084 rows=86 loops=21,039)

  • Recheck Cond: (geography_internal_id = g.geography_internal_id)
  • Filter: ((start_date <= '2020-01-01'::date) AND (end_date > '2020-01-01'::date) AND (request_id = 0) AND (team_internal_id = 69))
  • Rows Removed by Filter: 29
  • Heap Blocks: exact=2342991
11. 168.312 168.312 ↑ 1.6 114 21,039

Bitmap Index Scan on customer_alignment_geography_internal_id_idx (cost=0.00..5.76 rows=178 width=0) (actual time=0.008..0.008 rows=114 loops=21,039)

  • Index Cond: (geography_internal_id = g.geography_internal_id)
Planning time : 0.381 ms
Execution time : 10,152.036 ms