explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rcP2

Settings
# exclusive inclusive rows x rows loops node
1. 0.065 13,228.338 ↑ 25.0 4 1

GroupAggregate (cost=20,298.62..20,307.62 rows=100 width=52) (actual time=13,228.290..13,228.338 rows=4 loops=1)

  • Group Key: ((date_part('year'::text, to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision)))::integer), ((date_part('week'::text, to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision)))::integer), ((date_part('dow'::text, to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision)))::integer), (CASE WHEN (delivered_orders_stores.order_metadata = 'EXPRESS dashboard'::text) THEN (('Express '::text || (((delivered_orders_stores.dropoff_latest_time - delivered_orders_stores.dropoff_earliest_time) / 3600))::text) || 'h'::text) ELSE CASE WHEN ((generic_intervals.start_time_hour IS NULL) OR (generic_intervals.stop_time_hour IS NULL)) THEN NULL::text ELSE (to_char(to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision), 'HH24:MI-'::text) || to_char(to_timestamp((delivered_orders_stores.dropoff_latest_time)::double precision), 'HH24:MI'::text)) END END)
2. 0.063 13,228.273 ↑ 25.0 4 1

Sort (cost=20,298.62..20,298.87 rows=100 width=2,504) (actual time=13,228.268..13,228.273 rows=4 loops=1)

  • Sort Key: ((date_part('year'::text, to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision)))::integer), ((date_part('week'::text, to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision)))::integer), ((date_part('dow'::text, to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision)))::integer), (CASE WHEN (delivered_orders_stores.order_metadata = 'EXPRESS dashboard'::text) THEN (('Express '::text || (((delivered_orders_stores.dropoff_latest_time - delivered_orders_stores.dropoff_earliest_time) / 3600))::text) || 'h'::text) ELSE CASE WHEN ((generic_intervals.start_time_hour IS NULL) OR (generic_intervals.stop_time_hour IS NULL)) THEN NULL::text ELSE (to_char(to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision), 'HH24:MI-'::text) || to_char(to_timestamp((delivered_orders_stores.dropoff_latest_time)::double precision), 'HH24:MI'::text)) END END)
  • Sort Method: quicksort Memory: 39kB
3. 5,532.399 13,228.210 ↑ 25.0 4 1

Nested Loop Left Join (cost=401.93..20,295.29 rows=100 width=2,504) (actual time=1,620.887..13,228.210 rows=4 loops=1)

  • Join Filter: ((areas.id = ar.id) AND (generic_intervals.day_of_week = (date_part('dow'::text, to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision)))::integer) AND (generic_intervals.start_time_hour = (date_part('hour'::text, to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision)))::integer) AND (generic_intervals.start_time_minute = (date_part('minute'::text, to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision)))::integer) AND (generic_intervals.stop_time_hour = (date_part('hour'::text, to_timestamp((delivered_orders_stores.dropoff_latest_time)::double precision)))::integer) AND (generic_intervals.stop_time_minute = (date_part('minute'::text, to_timestamp((delivered_orders_stores.dropoff_latest_time)::double precision)))::integer))
  • Rows Removed by Join Filter: 18835032
  • Filter: (CASE WHEN (delivered_orders_stores.order_metadata = 'EXPRESS dashboard'::text) THEN (('Express '::text || (((delivered_orders_stores.dropoff_latest_time - delivered_orders_stores.dropoff_earliest_time) / 3600))::text) || 'h'::text) ELSE CASE WHEN ((generic_intervals.start_time_hour IS NULL) OR (generic_intervals.stop_time_hour IS NULL)) THEN NULL::text ELSE (to_char(to_timestamp((delivered_orders_stores.dropoff_earliest_time)::double precision), 'HH24:MI-'::text) || to_char(to_timestamp((delivered_orders_stores.dropoff_latest_time)::double precision), 'HH24:MI'::text)) END END IS NOT NULL)
  • Rows Removed by Filter: 37000
4. 62.036 3,773.387 ↓ 370.0 37,004 1

Hash Semi Join (cost=384.14..20,196.11 rows=100 width=2,460) (actual time=6.000..3,773.387 rows=37,004 loops=1)

  • Hash Cond: (ar.id = between_pricing_per_zone.zone_id)
5. 154.330 3,705.704 ↓ 514.9 113,279 1

Nested Loop (cost=0.14..19,810.42 rows=220 width=2,460) (actual time=0.107..3,705.704 rows=113,279 loops=1)

6. 127.188 127.188 ↓ 3.2 38,474 1

Seq Scan on delivered_orders_stores (cost=0.00..14,658.35 rows=12,019 width=668) (actual time=0.033..127.188 rows=38,474 loops=1)

  • Filter: (final_successful AND (sender_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid) AND (date_part('year'::text, to_timestamp((dropoff_earliest_time)::double precision)) > '2000'::double precision))
  • Rows Removed by Filter: 78297
7. 3,424.186 3,424.186 ↓ 3.0 3 38,474

Index Scan using area_geom_idx on areas ar (cost=0.14..0.42 rows=1 width=1,792) (actual time=0.018..0.089 rows=3 loops=38,474)

  • Index Cond: (geom ~ st_setsrid(st_makepoint(delivered_orders_stores.dropoff_lon, delivered_orders_stores.dropoff_lat), 4326))
  • Filter: _st_contains(geom, st_setsrid(st_makepoint(delivered_orders_stores.dropoff_lon, delivered_orders_stores.dropoff_lat), 4326))
  • Rows Removed by Filter: 5
8. 0.011 5.647 ↑ 1.3 19 1

Hash (cost=383.69..383.69 rows=25 width=8) (actual time=5.647..5.647 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 1.075 5.636 ↑ 1.3 19 1

HashAggregate (cost=383.19..383.44 rows=25 width=8) (actual time=5.630..5.636 rows=19 loops=1)

  • Group Key: between_pricing_per_zone.zone_id
10. 1.564 4.561 ↓ 21.6 3,563 1

Nested Loop (cost=4.77..382.77 rows=165 width=8) (actual time=0.086..4.561 rows=3,563 loops=1)

11. 0.047 0.085 ↓ 3.6 32 1

Bitmap Heap Scan on vendor_mapping_between_intervals (cost=4.35..32.11 rows=9 width=4) (actual time=0.043..0.085 rows=32 loops=1)

  • Recheck Cond: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
  • Heap Blocks: exact=7
12. 0.038 0.038 ↓ 5.1 46 1

Bitmap Index Scan on mapped_interval_retailer_idx (cost=0.00..4.35 rows=9 width=0) (actual time=0.038..0.038 rows=46 loops=1)

  • Index Cond: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
13. 2.912 2.912 ↓ 4.3 111 32

Index Only Scan using unique_prices_per_vendor_and_interval on between_pricing_per_zone (cost=0.42..38.70 rows=26 width=16) (actual time=0.011..0.091 rows=111 loops=32)

  • Index Cond: (interval_id = vendor_mapping_between_intervals.id)
  • Heap Fetches: 3563
14. 3,920.001 3,922.424 ↓ 169.7 509 37,004

Materialize (cost=17.79..58.94 rows=3 width=24) (actual time=0.000..0.106 rows=509 loops=37,004)

15. 0.697 2.423 ↓ 169.7 509 1

Nested Loop (cost=17.79..58.92 rows=3 width=24) (actual time=0.395..2.423 rows=509 loops=1)

16. 0.252 1.217 ↓ 169.7 509 1

Nested Loop (cost=17.65..58.42 rows=3 width=28) (actual time=0.342..1.217 rows=509 loops=1)

17. 0.035 0.453 ↓ 32.0 32 1

Nested Loop (cost=17.23..46.16 rows=1 width=28) (actual time=0.327..0.453 rows=32 loops=1)

18. 0.045 0.354 ↓ 32.0 32 1

Hash Join (cost=17.23..45.07 rows=1 width=24) (actual time=0.311..0.354 rows=32 loops=1)

  • Hash Cond: ((mapped_intervals.between_interval_id = generic_intervals.id) AND (mapped_intervals.day_of_week = generic_intervals.day_of_week))
19. 0.018 0.030 ↓ 3.6 32 1

Bitmap Heap Scan on vendor_mapping_between_intervals mapped_intervals (cost=4.35..32.11 rows=9 width=16) (actual time=0.015..0.030 rows=32 loops=1)

  • Recheck Cond: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
  • Heap Blocks: exact=7
20. 0.012 0.012 ↓ 5.1 46 1

Bitmap Index Scan on mapped_interval_retailer_idx (cost=0.00..4.35 rows=9 width=0) (actual time=0.012..0.012 rows=46 loops=1)

  • Index Cond: (vendor_store_id = '5647cca0-c8a1-11e8-b810-777ae174f124'::uuid)
21. 0.141 0.279 ↓ 1.0 406 1

Hash (cost=6.95..6.95 rows=395 width=24) (actual time=0.279..0.279 rows=406 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
22. 0.138 0.138 ↓ 1.0 406 1

Seq Scan on generic_between_intervals generic_intervals (cost=0.00..6.95 rows=395 width=24) (actual time=0.004..0.138 rows=406 loops=1)

23. 0.064 0.064 ↑ 1.0 1 32

Seq Scan on price_categories (cost=0.00..1.09 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=32)

  • Filter: (id = 1)
  • Rows Removed by Filter: 6
24. 0.512 0.512 ↓ 3.2 16 32

Index Only Scan using unique_prices_per_vendor_and_interval on between_pricing_per_zone zone_pricing (cost=0.42..12.21 rows=5 width=24) (actual time=0.006..0.016 rows=16 loops=32)

  • Index Cond: ((interval_id = mapped_intervals.id) AND (price_category_id = 1))
  • Heap Fetches: 509
25. 0.509 0.509 ↑ 1.0 1 509

Index Only Scan using areas_pkey on areas (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=509)

  • Index Cond: (id = zone_pricing.zone_id)
  • Heap Fetches: 509