explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L2B9

Settings
# exclusive inclusive rows x rows loops node
1. 1.091 2,423.659 ↑ 1.0 175 1

Sort (cost=108,017.17..108,017.62 rows=179 width=696) (actual time=2,423.632..2,423.659 rows=175 loops=1)

  • Sort Key: account_state_1.account_state_id, product_state_1.product_state_id, asset_1.asset_id
  • Sort Method: quicksort Memory: 199kB
2. 0.406 2,422.568 ↑ 1.0 175 1

Hash Left Join (cost=104,951.93..108,010.47 rows=179 width=696) (actual time=2,162.157..2,422.568 rows=175 loops=1)

  • Hash Cond: (account_state_1.account_state_id = account_extras_tx_1.account_state_id)
3. 0.376 2,422.158 ↑ 1.0 175 1

Hash Left Join (cost=104,887.93..107,945.79 rows=179 width=690) (actual time=2,162.115..2,422.158 rows=175 loops=1)

  • Hash Cond: (account_state_1.account_state_id = account_extras_es_1.account_state_id)
4. 0.879 2,421.774 ↑ 1.0 175 1

Nested Loop Left Join (cost=104,868.93..107,926.11 rows=179 width=516) (actual time=2,162.082..2,421.774 rows=175 loops=1)

5. 0.282 2,352.120 ↑ 1.0 175 1

Nested Loop Left Join (cost=104,868.50..107,273.59 rows=179 width=492) (actual time=2,160.274..2,352.120 rows=175 loops=1)

6. 0.278 2,182.138 ↑ 1.0 100 1

Nested Loop Left Join (cost=104,867.20..106,124.90 rows=100 width=200) (actual time=2,157.527..2,182.138 rows=100 loops=1)

7. 0.462 2,180.660 ↑ 1.0 100 1

Nested Loop (cost=104,866.76..105,714.83 rows=100 width=105) (actual time=2,157.509..2,180.660 rows=100 loops=1)

8. 0.062 2,157.298 ↑ 1.0 100 1

Limit (cost=104,866.33..104,868.08 rows=100 width=12) (actual time=2,156.632..2,157.298 rows=100 loops=1)

9. 0.426 2,157.236 ↑ 2.7 100 1

GroupAggregate (cost=104,866.33..104,871.04 rows=269 width=12) (actual time=2,156.630..2,157.236 rows=100 loops=1)

  • Group Key: member_state_1.member_id
10. 4.575 2,156.810 ↑ 1.3 201 1

Sort (cost=104,866.33..104,867.01 rows=269 width=12) (actual time=2,156.623..2,156.810 rows=201 loops=1)

  • Sort Key: member_state_1.member_id
  • Sort Method: quicksort Memory: 288kB
11. 9.955 2,152.235 ↓ 15.2 4,093 1

Nested Loop (cost=765.57..104,855.48 rows=269 width=12) (actual time=4.826..2,152.235 rows=4,093 loops=1)

12. 34.427 38.185 ↓ 14.1 14,511 1

Bitmap Heap Scan on address (cost=765.14..96,273.17 rows=1,028 width=4) (actual time=4.805..38.185 rows=14,511 loops=1)

  • Filter: ((postal_code)::text ~~ 'E2%%'::text)
  • Heap Blocks: exact=5766
13. 3.758 3.758 ↑ 2.3 14,511 1

Bitmap Index Scan on test_index_1 (cost=0.00..764.88 rows=33,245 width=0) (actual time=3.758..3.758 rows=14,511 loops=1)

  • Index Cond: (((postal_code)::text ~>=~ 'E2'::text) AND ((postal_code)::text ~<~ 'E3'::text))
14. 2,104.095 2,104.095 ↓ 0.0 0 14,511

Index Scan using ix_member_state_address_id on member_state member_state_1 (cost=0.43..8.34 rows=1 width=16) (actual time=0.144..0.145 rows=0 loops=14,511)

  • Index Cond: (address_id = address.address_id)
15. 22.900 22.900 ↑ 1.0 1 100

Index Scan using pk_member_state on member_state (cost=0.43..8.45 rows=1 width=105) (actual time=0.228..0.229 rows=1 loops=100)

  • Index Cond: (member_state_id = (max(member_state_1.member_state_id)))
16. 1.200 1.200 ↑ 1.0 1 100

Index Scan using pk_address on address address_1 (cost=0.43..4.09 rows=1 width=95) (actual time=0.012..0.012 rows=1 loops=100)

  • Index Cond: (address_id = member_state.address_id)
17. 0.564 169.700 ↑ 1.0 2 100

Nested Loop Left Join (cost=1.30..11.47 rows=2 width=292) (actual time=1.681..1.697 rows=2 loops=100)

18. 0.674 63.800 ↑ 1.0 1 100

Nested Loop Left Join (cost=0.86..7.71 rows=1 width=163) (actual time=0.635..0.638 rows=1 loops=100)

19. 62.100 62.100 ↑ 1.0 1 100

Index Scan using ix_account_state_member_state_id on account_state account_state_1 (cost=0.43..3.61 rows=1 width=68) (actual time=0.620..0.621 rows=1 loops=100)

  • Index Cond: (member_state.member_state_id = member_state_id)
20. 1.026 1.026 ↑ 1.0 1 114

Index Scan using pk_address on address address_2 (cost=0.43..4.09 rows=1 width=95) (actual time=0.009..0.009 rows=1 loops=114)

  • Index Cond: (address_id = account_state_1.billing_address_id)
21. 105.336 105.336 ↑ 2.0 2 114

Index Scan using ix_product_state_account_state_id on product_state product_state_1 (cost=0.43..3.72 rows=4 width=129) (actual time=0.915..0.924 rows=2 loops=114)

  • Index Cond: (account_state_1.account_state_id = account_state_id)
22. 68.775 68.775 ↑ 1.0 1 175

Index Scan using ix_asset_product_state_id on asset asset_1 (cost=0.43..3.64 rows=1 width=24) (actual time=0.393..0.393 rows=1 loops=175)

  • Index Cond: (product_state_1.product_state_id = product_state_id)
23. 0.001 0.008 ↓ 0.0 0 1

Hash (cost=14.00..14.00 rows=400 width=174) (actual time=0.008..0.008 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
24. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on account_extras_es account_extras_es_1 (cost=0.00..14.00 rows=400 width=174) (actual time=0.007..0.007 rows=0 loops=1)

25. 0.000 0.004 ↓ 0.0 0 1

Hash (cost=34.00..34.00 rows=2,400 width=6) (actual time=0.004..0.004 rows=0 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 32kB
26. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on account_extras_tx account_extras_tx_1 (cost=0.00..34.00 rows=2,400 width=6) (actual time=0.004..0.004 rows=0 loops=1)

Planning time : 2.562 ms
Execution time : 2,423.954 ms