explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5lIT

Settings
# exclusive inclusive rows x rows loops node
1. 6.410 28,589.740 ↑ 4.4 60 1

GroupAggregate (cost=157,064.13..157,074.69 rows=264 width=190) (actual time=28,582.974..28,589.740 rows=60 loops=1)

  • Group Key: lookup.county
2.          

CTE orders

3. 25,875.808 26,013.047 ↓ 2,170.0 2,170 1

Bitmap Heap Scan on orders_document_storage (cost=2,183.69..156,688.21 rows=1 width=3,070) (actual time=187.777..26,013.047 rows=2,170 loops=1)

  • Recheck Cond: (((created_at)::date >= (('now'::cstring)::date - 30)) AND ((created_at)::date <= ('now'::cstring)::date))
  • Filter: (((billing_address ->> 'country_code'::text) = 'US'::text) AND ((billing_address ->> 'province_code'::text) = 'FL'::text))
  • Rows Removed by Filter: 63247
  • Heap Blocks: exact=50647
4. 137.239 137.239 ↓ 1.6 82,809 1

Bitmap Index Scan on orders_document_storage_created_at_date_index (cost=0.00..2,183.69 rows=51,925 width=0) (actual time=137.239..137.239 rows=82,809 loops=1)

  • Index Cond: (((created_at)::date >= (('now'::cstring)::date - 30)) AND ((created_at)::date <= ('now'::cstring)::date))
5. 4.959 28,583.330 ↓ 9.0 2,388 1

Sort (cost=375.91..376.57 rows=264 width=157) (actual time=28,582.880..28,583.330 rows=2,388 loops=1)

  • Sort Key: lookup.county
  • Sort Method: quicksort Memory: 340kB
6. 9.962 28,578.371 ↓ 9.0 2,388 1

Nested Loop Left Join (cost=11.11..365.30 rows=264 width=157) (actual time=26,115.685..28,578.371 rows=2,388 loops=1)

7. 771.409 28,527.179 ↓ 2,170.0 2,170 1

Nested Loop Left Join (cost=4.78..4.84 rows=1 width=142) (actual time=26,115.654..28,527.179 rows=2,170 loops=1)

  • Join Filter: (orders.id = orders_1.id)
  • Rows Removed by Join Filter: 4706730
8. 194.600 194.600 ↓ 2,170.0 2,170 1

CTE Scan on orders (cost=0.00..0.02 rows=1 width=86) (actual time=187.794..194.600 rows=2,170 loops=1)

9. 1,660.682 27,561.170 ↓ 2,170.0 2,170 2,170

HashAggregate (cost=4.78..4.79 rows=1 width=72) (actual time=11.948..12.701 rows=2,170 loops=2,170)

  • Group Key: orders_1.id
10. 6.072 25,900.488 ↓ 42.1 4,215 1

Nested Loop Left Join (cost=0.00..2.53 rows=100 width=40) (actual time=0.025..25,900.488 rows=4,215 loops=1)

11. 25,870.546 25,870.546 ↓ 2,170.0 2,170 1

CTE Scan on orders orders_1 (cost=0.00..0.02 rows=1 width=40) (actual time=0.004..25,870.546 rows=2,170 loops=1)

12. 23.870 23.870 ↑ 50.0 2 2,170

Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.010..0.011 rows=2 loops=2,170)

13. 6.510 41.230 ↑ 264.0 1 2,170

Bitmap Heap Scan on _us_zip_county_lookup lookup (cost=6.34..357.80 rows=264 width=47) (actual time=0.018..0.019 rows=1 loops=2,170)

  • Recheck Cond: (((regexp_matches((orders.billing_address ->> 'zip'::text), '(\d+)[-]?'::text))[1]) = zip)
  • Heap Blocks: exact=2380
14. 34.720 34.720 ↑ 264.0 1 2,170

Bitmap Index Scan on _us_zip_county_lookup_zip_index (cost=0.00..6.27 rows=264 width=0) (actual time=0.016..0.016 rows=1 loops=2,170)

  • Index Cond: (((regexp_matches((orders.billing_address ->> 'zip'::text), '(\d+)[-]?'::text))[1]) = zip)
Planning time : 1.145 ms
Execution time : 28,603.664 ms