explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C5vH

Settings
# exclusive inclusive rows x rows loops node
1. 1.550 624.025 ↓ 1.1 200 1

Sort (cost=11,929.45..11,929.90 rows=179 width=696) (actual time=623.995..624.025 rows=200 loops=1)

  • Sort Key: account_state_1.account_state_id, product_state_1.product_state_id, asset_1.asset_id
  • Sort Method: quicksort Memory: 227kB
2. 0.360 622.475 ↓ 1.1 200 1

Hash Left Join (cost=8,864.21..11,922.75 rows=179 width=696) (actual time=128.858..622.475 rows=200 loops=1)

  • Hash Cond: (account_state_1.account_state_id = account_extras_tx_1.account_state_id)
3. 0.531 622.112 ↓ 1.1 200 1

Hash Left Join (cost=8,800.21..11,858.07 rows=179 width=690) (actual time=128.841..622.112 rows=200 loops=1)

  • Hash Cond: (account_state_1.account_state_id = account_extras_es_1.account_state_id)
4. 1.402 621.576 ↓ 1.1 200 1

Nested Loop Left Join (cost=8,781.21..11,838.39 rows=179 width=516) (actual time=128.799..621.576 rows=200 loops=1)

5. 0.567 481.374 ↓ 1.1 200 1

Nested Loop Left Join (cost=8,780.78..11,185.87 rows=179 width=492) (actual time=128.784..481.374 rows=200 loops=1)

6. 0.454 207.307 ↑ 1.0 100 1

Nested Loop Left Join (cost=8,779.48..10,037.18 rows=100 width=200) (actual time=128.723..207.307 rows=100 loops=1)

7. 0.478 201.953 ↑ 1.0 100 1

Nested Loop (cost=8,779.04..9,627.11 rows=100 width=105) (actual time=128.708..201.953 rows=100 loops=1)

8. 0.087 129.475 ↑ 1.0 100 1

Limit (cost=8,778.61..8,780.36 rows=100 width=12) (actual time=128.687..129.475 rows=100 loops=1)

9. 0.563 129.388 ↑ 2.7 100 1

GroupAggregate (cost=8,778.61..8,783.32 rows=269 width=12) (actual time=128.685..129.388 rows=100 loops=1)

  • Group Key: member_state_1.member_id
10. 0.778 128.825 ↑ 1.3 201 1

Sort (cost=8,778.61..8,779.29 rows=269 width=12) (actual time=128.678..128.825 rows=201 loops=1)

  • Sort Key: member_state_1.member_id
  • Sort Method: quicksort Memory: 68kB
11. 1.103 128.047 ↓ 3.4 928 1

Nested Loop (cost=0.86..8,767.76 rows=269 width=12) (actual time=1.766..128.047 rows=928 loops=1)

12. 17.184 17.184 ↓ 3.3 3,430 1

Index Scan using test_index_1 on address (cost=0.43..185.45 rows=1,028 width=4) (actual time=0.222..17.184 rows=3,430 loops=1)

  • Index Cond: (((postal_code)::text ~>=~ 'TW3'::text) AND ((postal_code)::text ~<~ 'TW4'::text))
  • Filter: ((postal_code)::text ~~ 'TW3%%'::text)
13. 109.760 109.760 ↓ 0.0 0 3,430

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.032..0.032 rows=0 loops=3,430)

  • Index Cond: (address_id = address.address_id)
14. 72.000 72.000 ↑ 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.718..0.720 rows=1 loops=100)

  • Index Cond: (member_state_id = (max(member_state_1.member_state_id)))
15. 4.900 4.900 ↑ 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.049..0.049 rows=1 loops=100)

  • Index Cond: (address_id = member_state.address_id)
16. 0.658 273.500 ↑ 1.0 2 100

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

17. 0.805 99.500 ↑ 1.0 1 100

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

18. 97.000 97.000 ↑ 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.968..0.970 rows=1 loops=100)

  • Index Cond: (member_state.member_state_id = member_state_id)
19. 1.695 1.695 ↑ 1.0 1 113

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

  • Index Cond: (address_id = account_state_1.billing_address_id)
20. 173.342 173.342 ↑ 2.0 2 113

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=1.525..1.534 rows=2 loops=113)

  • Index Cond: (account_state_1.account_state_id = account_state_id)
21. 138.800 138.800 ↑ 1.0 1 200

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

  • Index Cond: (product_state_1.product_state_id = product_state_id)
22. 0.001 0.005 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
23. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

24. 0.001 0.003 ↓ 0.0 0 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 32kB
25. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

Planning time : 2.080 ms
Execution time : 624.459 ms