explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GTSc

Settings
# exclusive inclusive rows x rows loops node
1. 188.032 24,082.972 ↑ 598.0 102,811 1

Nested Loop (cost=665,916.25..135,965,548.55 rows=61,485,631 width=344) (actual time=14,779.829..24,082.972 rows=102,811 loops=1)

2. 113.713 14,950.383 ↑ 598.0 102,811 1

Merge Join (cost=665,914.10..1,588,702.00 rows=61,485,631 width=394) (actual time=14,778.770..14,950.383 rows=102,811 loops=1)

  • Merge Cond: (((COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text) = (appuseraccount.account)::text)
3. 260.488 14,657.954 ↓ 1.0 104,122 1

Sort (cost=650,487.82..650,739.54 rows=100,688 width=264) (actual time=14,624.289..14,657.954 rows=104,122 loops=1)

  • Sort Key: ((COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
  • Sort Method: quicksort Memory: 55805kB
4. 93.093 14,397.466 ↓ 1.0 104,122 1

Merge Join (cost=8.96..642,120.88 rows=100,688 width=264) (actual time=1.117..14,397.466 rows=104,122 loops=1)

  • Merge Cond: ((houseinfo.houseid)::text = (lockkeyinfo.houseid)::text)
5. 65.759 13,458.681 ↑ 1.0 77,823 1

Nested Loop (cost=8.54..636,227.18 rows=77,830 width=186) (actual time=0.849..13,458.681 rows=77,823 loops=1)

6. 318.658 318.658 ↑ 1.0 77,823 1

Index Scan using "pk_houseinfo_1570385679_119 " on houseinfo (cost=0.42..1,732.47 rows=77,830 width=146) (actual time=0.007..318.658 rows=77,823 loops=1)

7. 1,556.460 13,074.264 ↑ 1.0 1 77,823

Aggregate (cost=8.12..8.13 rows=1 width=40) (actual time=0.168..0.168 rows=1 loops=77,823)

8. 11,517.804 11,517.804 ↑ 3.5 14 77,823

Index Scan using opendoorrecord_houseid_idx on opendoorrecord (cost=0.43..6.28 rows=49 width=20) (actual time=0.021..0.148 rows=14 loops=77,823)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
9. 845.692 845.692 ↓ 1.0 104,146 1

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..4,454.39 rows=100,998 width=78) (actual time=0.026..845.692 rows=104,146 loops=1)

  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 22266
10. 110.963 178.716 ↓ 1.3 164,681 1

Sort (cost=15,426.28..15,731.60 rows=122,131 width=154) (actual time=154.455..178.716 rows=164,681 loops=1)

  • Sort Key: appuseraccount.account
  • Sort Method: quicksort Memory: 12675kB
11. 14.711 67.753 ↓ 1.0 122,160 1

Append (cost=0.00..3,886.07 rows=122,131 width=154) (actual time=0.018..67.753 rows=122,160 loops=1)

12. 51.051 51.051 ↑ 1.0 118,285 1

Seq Scan on appuseraccount (cost=0.00..2,000.32 rows=118,292 width=71) (actual time=0.017..51.051 rows=118,285 loops=1)

13. 0.650 1.991 ↓ 1.0 3,875 1

Subquery Scan on *SELECT* 2 (cost=0.00..92.18 rows=3,839 width=78) (actual time=0.013..1.991 rows=3,875 loops=1)

14. 1.341 1.341 ↓ 1.0 3,875 1

Seq Scan on bluekeyuserinfo (cost=0.00..53.79 rows=3,839 width=104) (actual time=0.011..1.341 rows=3,875 loops=1)

15. 411.244 8,944.557 ↑ 1.0 1 102,811

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual time=0.087..0.087 rows=1 loops=102,811)

16. 1,439.354 8,533.313 ↓ 9.0 9 102,811

Bitmap Heap Scan on opendoorrecord opendoorrecord_1 (cost=2.03..2.15 rows=1 width=20) (actual time=0.071..0.083 rows=9 loops=102,811)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = (lockkeyinfo.houseid)::text))
  • Heap Blocks: exact=910234
17. 214.461 7,093.959 ↓ 0.0 0 102,811

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.069..0.069 rows=0 loops=102,811)

18. 6,168.660 6,168.660 ↓ 1.1 53 102,811

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual time=0.060..0.060 rows=53 loops=102,811)

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
19. 710.838 710.838 ↑ 1.4 34 78,982

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual time=0.009..0.009 rows=34 loops=78,982)

  • Index Cond: ((houseid)::text = (lockkeyinfo.houseid)::text)
Planning time : 1.035 ms
Execution time : 24,097.979 ms