explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aTSx

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 1,093.447 ↑ 1.0 20 1

Limit (cost=5,626.33..118,152.70 rows=20 width=170) (actual time=71.097..1,093.447 rows=20 loops=1)

2. 0.123 1,093.427 ↑ 2,596.7 20 1

Nested Loop (cost=5,626.33..292,202,843.94 rows=51,934 width=170) (actual time=71.095..1,093.427 rows=20 loops=1)

3. 0.071 1.024 ↑ 2,596.7 20 1

Nested Loop (cost=8.12..424,843.23 rows=51,934 width=186) (actual time=0.027..1.024 rows=20 loops=1)

4. 0.053 0.053 ↑ 2,596.7 20 1

Seq Scan on houseinfo (cost=0.00..1,460.88 rows=51,934 width=146) (actual time=0.009..0.053 rows=20 loops=1)

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 12
5. 0.380 0.900 ↑ 1.0 1 20

Aggregate (cost=8.12..8.13 rows=1 width=40) (actual time=0.045..0.045 rows=1 loops=20)

6. 0.520 0.520 ↑ 4.5 11 20

Index Scan using opendoorrecord_houseid_idx on opendoorrecord (cost=0.43..6.28 rows=49 width=20) (actual time=0.012..0.026 rows=11 loops=20)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
7. 0.200 1,092.280 ↑ 1.0 1 20

Aggregate (cost=5,618.21..5,618.22 rows=1 width=32) (actual time=54.614..54.614 rows=1 loops=20)

8. 0.220 1,092.080 ↑ 610.5 2 20

Nested Loop (cost=0.94..5,615.16 rows=1,221 width=32) (actual time=18.203..54.604 rows=2 loops=20)

9. 230.399 1,091.860 ↑ 610.5 2 20

Hash Join (cost=0.94..5,578.53 rows=1,221 width=124) (actual time=18.194..54.593 rows=2 loops=20)

  • Hash Cond: ((appuseraccount.account)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
10. 219.861 861.101 ↓ 1.0 122,160 17

Append (cost=0.00..3,886.07 rows=122,131 width=154) (actual time=0.003..50.653 rows=122,160 loops=17)

11. 612.527 612.527 ↑ 1.0 118,285 17

Seq Scan on appuseraccount (cost=0.00..2,000.32 rows=118,292 width=71) (actual time=0.003..36.031 rows=118,285 loops=17)

12. 9.571 28.713 ↓ 1.0 3,875 17

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

13. 19.142 19.142 ↓ 1.0 3,875 17

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

14. 0.040 0.360 ↑ 1.0 2 20

Hash (cost=0.91..0.91 rows=2 width=58) (actual time=0.018..0.018 rows=2 loops=20)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.320 0.320 ↑ 1.0 2 20

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.91 rows=2 width=58) (actual time=0.013..0.016 rows=2 loops=20)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 0
16. 0.000 0.000 ↑ 1.0 1 30

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=30)

Planning time : 0.805 ms
Execution time : 1,093.550 ms