explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XeRX

Settings
# exclusive inclusive rows x rows loops node
1. 23.869 1,458.880 ↓ 5,450.0 5,450 1

GroupAggregate (cost=28,819.85..28,819.96 rows=1 width=79) (actual time=1,427.608..1,458.880 rows=5,450 loops=1)

  • Group Key: l.id, la.value
2. 48.141 1,435.011 ↓ 98,159.0 98,159 1

Sort (cost=28,819.85..28,819.86 rows=1 width=79) (actual time=1,427.586..1,435.011 rows=98,159 loops=1)

  • Sort Key: l.id, la.value
  • Sort Method: quicksort Memory: 16876kB
3. 23.291 1,386.870 ↓ 98,159.0 98,159 1

Nested Loop (cost=693.62..28,819.84 rows=1 width=79) (actual time=4.123..1,386.870 rows=98,159 loops=1)

4. 10.749 425.829 ↓ 17,050.0 17,050 1

Nested Loop (cost=693.05..28,766.56 rows=1 width=95) (actual time=4.098..425.829 rows=17,050 loops=1)

  • Join Filter: (sbm.partition_id = pbm.id)
5. 12.242 398.030 ↓ 17,050.0 17,050 1

Nested Loop (cost=693.05..28,764.85 rows=1 width=103) (actual time=4.090..398.030 rows=17,050 loops=1)

6. 5.285 249.388 ↓ 17,050.0 17,050 1

Nested Loop (cost=692.34..28,751.77 rows=1 width=61) (actual time=4.065..249.388 rows=17,050 loops=1)

  • Join Filter: (lsr.locality_id = l.id)
7. 12.485 158.853 ↓ 129.2 17,050 1

Hash Join (cost=691.77..28,207.35 rows=132 width=24) (actual time=4.049..158.853 rows=17,050 loops=1)

  • Hash Cond: (lsbm.subset_id = sbm.id)
8. 26.765 146.331 ↓ 5.2 94,860 1

Nested Loop (cost=679.37..28,142.60 rows=18,248 width=24) (actual time=4.002..146.331 rows=94,860 loops=1)

9. 4.869 25.936 ↓ 6.3 31,210 1

Nested Loop (cost=678.94..14,744.84 rows=4,952 width=8) (actual time=3.987..25.936 rows=31,210 loops=1)

10. 0.002 0.014 ↑ 1.0 1 1

Nested Loop (cost=0.27..6.10 rows=1 width=8) (actual time=0.012..0.014 rows=1 loops=1)

  • Join Filter: (sr.partition_id = pr.id)
11. 0.007 0.007 ↑ 1.0 1 1

Index Scan using pk_subset on subset sr (cost=0.27..4.38 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (id = 41)
12. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on partition pr (cost=0.00..1.61 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Filter: ((name)::text = 'REGIONS'::text)
  • Rows Removed by Filter: 2
13. 17.808 21.053 ↓ 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.972..21.053 rows=31,210 loops=1)

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

  • Index Cond: (subset_id = 41)
15. 93.630 93.630 ↑ 1.0 3 31,210

Index Only Scan using locality_subset_pkey on locality_subset lsbm (cost=0.43..2.41 rows=3 width=16) (actual time=0.003..0.003 rows=3 loops=31,210)

  • Index Cond: (locality_id = lsr.locality_id)
  • Heap Fetches: 32560
16. 0.003 0.037 ↑ 1.0 3 1

Hash (cost=12.09..12.09 rows=3 width=16) (actual time=0.037..0.037 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.008 0.034 ↑ 1.0 3 1

Bitmap Heap Scan on subset sbm (cost=6.84..12.09 rows=3 width=16) (actual time=0.031..0.034 rows=3 loops=1)

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

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

  • Index Cond: ((name)::text = ANY ('{FULL_SERVICE,MKT_DELIVERY,EXPRESS_MARKET}'::text[]))
19. 85.250 85.250 ↑ 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.005..0.005 rows=1 loops=17,050)

  • Index Cond: (id = lsbm.locality_id)
20. 0.000 136.400 ↑ 4.0 1 17,050

Nested Loop (cost=0.71..12.67 rows=4 width=42) (actual time=0.008..0.008 rows=1 loops=17,050)

21. 51.150 51.150 ↑ 1.0 1 17,050

Index Scan using unique_name_ak on attribute_key ak (cost=0.14..4.25 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=17,050)

  • Index Cond: ((name)::text = 'ORIGIN_EXTERNAL_UUID'::text)
22. 85.250 85.250 ↑ 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.005..0.005 rows=1 loops=17,050)

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

Seq Scan on partition pbm (cost=0.00..1.61 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=17,050)

  • Filter: ((name)::text = 'BUSINESS_MODEL'::text)
  • Rows Removed by Filter: 3
24. 937.750 937.750 ↑ 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.005..0.055 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 : 7.232 ms
Execution time : 1,459.313 ms