explain.depesz.com

PostgreSQL's explain analyze made readable

Result: idU0

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=426,636,262.26..426,636,262.31 rows=20 width=170) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=426,636,262.26..426,636,392.06 rows=51,922 width=170) (actual rows= loops=)

  • Sort Key: houseinfo.xzqhdm, (((houseinfo.detailaddr)::text || (houseinfo.housenumber)::text))
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8,216.75..426,634,880.63 rows=51,922 width=170) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7.97..416,697.37 rows=51,922 width=186) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Seq Scan on houseinfo (cost=0.00..1,460.56 rows=51,922 width=146) (actual rows= loops=)

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
6. 0.000 0.000 ↓ 0.0

Aggregate (cost=7.97..7.98 rows=1 width=40) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Index Scan using opendoorrecord_houseid_idx on opendoorrecord (cost=0.43..6.17 rows=48 width=20) (actual rows= loops=)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
8. 0.000 0.000 ↓ 0.0

Aggregate (cost=8,208.78..8,208.79 rows=1 width=32) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.95..5,576.92 rows=1,221 width=188) (actual rows= loops=)

  • Hash Cond: ((appuseraccount.account)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
10. 0.000 0.000 ↓ 0.0

Append (cost=0.00..3,884.92 rows=122,097 width=154) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on appuseraccount (cost=0.00..1,999.68 rows=118,258 width=71) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

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

13. 0.000 0.000 ↓ 0.0

Seq Scan on bluekeyuserinfo (cost=0.00..53.79 rows=3,839 width=104) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=0.92..0.92 rows=2 width=58) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.92 rows=2 width=58) (actual rows= loops=)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Filter: ((userrole <> 255) AND (keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
16.          

SubPlan (for Aggregate)

17. 0.000 0.000 ↓ 0.0

Aggregate (cost=2.14..2.15 rows=1 width=32) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on opendoorrecord opendoorrecord_1 (cost=2.03..2.14 rows=1 width=20) (actual rows= loops=)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = (lockkeyinfo.houseid)::text))
19. 0.000 0.000 ↓ 0.0

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

20. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
21. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.89 rows=48 width=0) (actual rows= loops=)

  • Index Cond: ((houseid)::text = (lockkeyinfo.houseid)::text)