explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6d33

Settings
# exclusive inclusive rows x rows loops node
1. 12.122 594.375 ↓ 5,452.0 5,452 1

GroupAggregate (cost=34,996.60..34,996.72 rows=1 width=79) (actual time=577.422..594.375 rows=5,452 loops=1)

  • Group Key: l.id, la.value
2. 34.961 582.253 ↓ 98,198.0 98,198 1

Sort (cost=34,996.60..34,996.61 rows=1 width=79) (actual time=577.407..582.253 rows=98,198 loops=1)

  • Sort Key: l.id, la.value
  • Sort Method: quicksort Memory: 16882kB
3. 18.410 547.292 ↓ 98,198.0 98,198 1

Nested Loop (cost=19,481.21..34,996.59 rows=1 width=79) (actual time=135.540..547.292 rows=98,198 loops=1)

4. 5.795 324.282 ↓ 17,050.0 17,050 1

Nested Loop (cost=19,480.63..34,943.31 rows=1 width=95) (actual time=135.511..324.282 rows=17,050 loops=1)

5. 2.139 301.437 ↓ 17,050.0 17,050 1

Nested Loop (cost=19,480.36..34,938.83 rows=1 width=103) (actual time=135.498..301.437 rows=17,050 loops=1)

6. 0.013 0.013 ↑ 1.0 1 1

Index Scan using unique_name_ak on attribute_key ak (cost=0.14..4.25 rows=1 width=8) (actual time=0.011..0.013 rows=1 loops=1)

  • Index Cond: ((name)::text = 'ORIGIN_EXTERNAL_UUID'::text)
7. 0.682 299.285 ↓ 5,683.3 17,050 1

Nested Loop (cost=19,480.22..34,934.28 rows=3 width=111) (actual time=135.484..299.285 rows=17,050 loops=1)

8. 1.899 230.403 ↓ 2,841.7 17,050 1

Nested Loop (cost=19,479.65..34,883.75 rows=6 width=61) (actual time=135.469..230.403 rows=17,050 loops=1)

  • Join Filter: (lsr.locality_id = l.id)
9. 12.804 160.304 ↓ 21.5 17,050 1

Hash Join (cost=19,479.08..31,613.10 rows=793 width=24) (actual time=135.446..160.304 rows=17,050 loops=1)

  • Hash Cond: (lsr.locality_id = lsbm.locality_id)
10. 12.810 15.408 ↓ 1.1 31,210 1

Bitmap Heap Scan on locality_subset lsr (cost=678.67..11,767.84 rows=29,709 width=16) (actual time=3.329..15.408 rows=31,210 loops=1)

  • Recheck Cond: (subset_id = 41)
  • Heap Blocks: exact=5262
11. 2.598 2.598 ↓ 1.1 31,299 1

Bitmap Index Scan on idx_locality_subset_subset_id (cost=0.00..671.25 rows=29,709 width=0) (actual time=2.598..2.598 rows=31,299 loops=1)

  • Index Cond: (subset_id = 41)
12. 28.759 132.092 ↓ 15.8 149,416 1

Hash (cost=17,834.04..17,834.04 rows=9,428 width=8) (actual time=132.092..132.092 rows=149,416 loops=1)

  • Buckets: 262144 (originally 16384) Batches: 1 (originally 1) Memory Usage: 7885kB
13. 24.422 103.333 ↓ 15.8 149,416 1

Nested Loop (cost=7.27..17,834.04 rows=9,428 width=8) (actual time=0.040..103.333 rows=149,416 loops=1)

14. 0.007 0.026 ↑ 1.0 3 1

Bitmap Heap Scan on subset sbm (cost=6.84..12.09 rows=3 width=8) (actual time=0.022..0.026 rows=3 loops=1)

  • Recheck Cond: ((name)::text = ANY ('{FULL_SERVICE,MKT_DELIVERY,EXPRESS_MARKET}'::text[]))
  • Heap Blocks: exact=2
15. 0.019 0.019 ↑ 1.0 3 1

Bitmap Index Scan on unique_name_ss (cost=0.00..6.84 rows=3 width=0) (actual time=0.019..0.019 rows=3 loops=1)

  • Index Cond: ((name)::text = ANY ('{FULL_SERVICE,MKT_DELIVERY,EXPRESS_MARKET}'::text[]))
16. 78.885 78.885 ↓ 10.8 49,805 3

Index Scan using idx_locality_subset_subset_id on locality_subset lsbm (cost=0.43..5,479.25 rows=4,614 width=16) (actual time=0.019..26.295 rows=49,805 loops=3)

  • Index Cond: (subset_id = sbm.id)
17. 68.200 68.200 ↑ 1.0 1 17,050

Index Scan using pk_locality on locality l (cost=0.56..4.02 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=17,050)

  • Index Cond: (id = lsbm.locality_id)
18. 68.200 68.200 ↑ 4.0 1 17,050

Index Scan using locality_attribute_locality_id_attribute_key_id_key on locality_attribute la (cost=0.57..8.02 rows=4 width=50) (actual time=0.004..0.004 rows=1 loops=17,050)

  • Index Cond: ((locality_id = l.id) AND (attribute_key_id = ak.id))
19. 17.050 17.050 ↑ 1.0 1 17,050

Index Only Scan using pk_subset on subset sr (cost=0.27..4.38 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=17,050)

  • Index Cond: (id = 41)
  • Heap Fetches: 17050
20. 204.600 204.600 ↑ 3.0 6 17,050

Index Scan using order_delivery_origin_id_created_date_idx on order_delivery od (cost=0.57..51.48 rows=18 width=16) (actual time=0.004..0.012 rows=6 loops=17,050)

  • Index Cond: ((origin_id = l.id) AND (created_date >= (CURRENT_DATE - 2)))
  • Filter: (NOT is_test)
  • Rows Removed by Filter: 0
Planning time : 11.596 ms
Execution time : 594.712 ms