explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bb3E

Settings
# exclusive inclusive rows x rows loops node
1. 24.190 2,374.054 ↓ 5,449.0 5,449 1

GroupAggregate (cost=34,996.60..34,996.71 rows=1 width=79) (actual time=2,341.475..2,374.054 rows=5,449 loops=1)

  • Group Key: l.id, la.value
2. 53.709 2,349.864 ↓ 97,056.0 97,056 1

Sort (cost=34,996.60..34,996.61 rows=1 width=79) (actual time=2,341.453..2,349.864 rows=97,056 loops=1)

  • Sort Key: l.id, la.value
  • Sort Method: quicksort Memory: 16721kB
3. 29.465 2,296.155 ↓ 97,056.0 97,056 1

Nested Loop (cost=19,481.21..34,996.59 rows=1 width=79) (actual time=1,371.269..2,296.155 rows=97,056 loops=1)

4. 3.128 1,652.926 ↓ 17,049.0 17,049 1

Nested Loop (cost=19,480.63..34,943.31 rows=1 width=95) (actual time=1,371.240..1,652.926 rows=17,049 loops=1)

5. 2.914 1,615.700 ↓ 17,049.0 17,049 1

Nested Loop (cost=19,480.36..34,938.83 rows=1 width=103) (actual time=1,371.207..1,615.700 rows=17,049 loops=1)

6. 0.012 0.012 ↑ 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.012 rows=1 loops=1)

  • Index Cond: ((name)::text = 'ORIGIN_EXTERNAL_UUID'::text)
7. 1.293 1,612.774 ↓ 5,683.0 17,049 1

Nested Loop (cost=19,480.22..34,934.28 rows=3 width=111) (actual time=1,371.192..1,612.774 rows=17,049 loops=1)

8. 17.851 1,509.187 ↓ 2,841.5 17,049 1

Nested Loop (cost=19,479.65..34,883.75 rows=6 width=61) (actual time=1,371.179..1,509.187 rows=17,049 loops=1)

  • Join Filter: (lsr.locality_id = l.id)
9. 17.050 1,406.091 ↓ 21.5 17,049 1

Hash Join (cost=19,479.08..31,613.10 rows=793 width=24) (actual time=1,371.155..1,406.091 rows=17,049 loops=1)

  • Hash Cond: (lsr.locality_id = lsbm.locality_id)
10. 18.649 21.236 ↓ 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.325..21.236 rows=31,210 loops=1)

  • Recheck Cond: (subset_id = 41)
  • Heap Blocks: exact=5262
11. 2.587 2.587 ↓ 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.587..2.587 rows=31,299 loops=1)

  • Index Cond: (subset_id = 41)
12. 34.183 1,367.805 ↓ 15.8 149,408 1

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

  • Buckets: 262144 (originally 16384) Batches: 1 (originally 1) Memory Usage: 7885kB
13. 24.330 1,333.622 ↓ 15.8 149,408 1

Nested Loop (cost=7.27..17,834.04 rows=9,428 width=8) (actual time=2.130..1,333.622 rows=149,408 loops=1)

14. 0.012 1.421 ↑ 1.0 3 1

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

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

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

  • Index Cond: ((name)::text = ANY ('{FULL_SERVICE,MKT_DELIVERY,EXPRESS_MARKET}'::text[]))
16. 1,307.871 1,307.871 ↓ 10.8 49,803 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.459..435.957 rows=49,803 loops=3)

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

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

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

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.005..0.006 rows=1 loops=17,049)

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

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

  • Index Cond: (id = 41)
  • Heap Fetches: 17049
20. 613.764 613.764 ↑ 3.0 6 17,049

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.006..0.036 rows=6 loops=17,049)

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