explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 80qmZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.702 8,979.790 ↑ 1.0 122 1

Nested Loop (cost=8,251.87..1,006,763.01 rows=122 width=177) (actual time=81.717..8,979.790 rows=122 loops=1)

2. 0.397 13.674 ↑ 1.0 122 1

Nested Loop (cost=8.54..1,072.57 rows=122 width=193) (actual time=0.131..13.674 rows=122 loops=1)

3. 0.609 2.419 ↑ 1.0 122 1

Nested Loop (cost=0.42..77.99 rows=122 width=187) (actual time=0.027..2.419 rows=122 loops=1)

4. 0.102 0.102 ↑ 1.0 122 1

Seq Scan on _regulated_house_alpub (cost=0.00..1.42 rows=122 width=41) (actual time=0.006..0.102 rows=122 loops=1)

5. 1.708 1.708 ↑ 1.0 1 122

Index Scan using "pk_houseinfo_1570385679_119 " on houseinfo (cost=0.42..0.63 rows=1 width=146) (actual time=0.014..0.014 rows=1 loops=122)

  • Index Cond: ((houseid)::text = _regulated_house_alpub.houseid)
6. 5.002 10.858 ↑ 1.0 1 122

Aggregate (cost=8.12..8.13 rows=1 width=40) (actual time=0.089..0.089 rows=1 loops=122)

7. 5.856 5.856 ↑ 1.8 27 122

Index Scan using opendoorrecord_houseid_idx on opendoorrecord (cost=0.43..6.28 rows=49 width=20) (actual time=0.011..0.048 rows=27 loops=122)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
8. 5.490 8,965.414 ↑ 1.0 1 122

Aggregate (cost=8,243.33..8,243.34 rows=1 width=32) (actual time=73.486..73.487 rows=1 loops=122)

9. 2.383 8,959.924 ↑ 407.0 3 122

Nested Loop (cost=3.09..8,240.27 rows=1,221 width=96) (actual time=20.505..73.442 rows=3 loops=122)

10. 1,877.702 8,939.428 ↑ 407.0 3 122

Hash Join (cost=0.94..5,577.88 rows=1,221 width=156) (actual time=20.440..73.274 rows=3 loops=122)

  • Hash Cond: ((appuseraccount.account)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
11. 1,730.692 7,058.920 ↓ 1.0 122,153 122

Append (cost=0.00..3,885.62 rows=122,117 width=154) (actual time=0.004..57.860 rows=122,153 loops=122)

12. 5,090.938 5,090.938 ↑ 1.0 118,278 122

Seq Scan on appuseraccount (cost=0.00..2,000.08 rows=118,278 width=71) (actual time=0.003..41.729 rows=118,278 loops=122)

13. 77.836 237.290 ↓ 1.0 3,875 122

Subquery Scan on *SELECT* 2 (cost=0.00..92.18 rows=3,839 width=78) (actual time=0.005..1.945 rows=3,875 loops=122)

14. 159.454 159.454 ↓ 1.0 3,875 122

Seq Scan on bluekeyuserinfo (cost=0.00..53.79 rows=3,839 width=104) (actual time=0.004..1.307 rows=3,875 loops=122)

15. 0.488 2.806 ↓ 1.5 3 122

Hash (cost=0.91..0.91 rows=2 width=58) (actual time=0.023..0.023 rows=3 loops=122)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 2.318 2.318 ↓ 1.5 3 122

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.91 rows=2 width=58) (actual time=0.014..0.019 rows=3 loops=122)

  • Index Cond: (_regulated_house_alpub.houseid = (houseid)::text)
  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 0
17. 3.070 18.113 ↑ 1.0 1 307

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual time=0.059..0.059 rows=1 loops=307)

18. 6.140 15.043 ↓ 10.0 10 307

Bitmap Heap Scan on opendoorrecord opendoorrecord_1 (cost=2.03..2.15 rows=1 width=20) (actual time=0.034..0.049 rows=10 loops=307)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = (lockkeyinfo.houseid)::text))
  • Heap Blocks: exact=3,016
19. 1.384 8.903 ↓ 0.0 0 307

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.029..0.029 rows=0 loops=307)

20. 4.912 4.912 ↑ 2.2 22 307

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual time=0.016..0.016 rows=22 loops=307)

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
21. 2.607 2.607 ↑ 1.4 35 237

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual time=0.011..0.011 rows=35 loops=237)

  • Index Cond: ((houseid)::text = (lockkeyinfo.houseid)::text)
Planning time : 1.210 ms
Execution time : 8,980.060 ms