explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B7la

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 494.126 ↑ 1.0 20 1

Limit (cost=5,577.26..117,261.38 rows=20 width=138) (actual time=8.079..494.126 rows=20 loops=1)

2. 0.142 494.111 ↑ 2,596.1 20 1

Nested Loop (cost=5,577.26..289,948,696.92 rows=51,922 width=138) (actual time=8.078..494.111 rows=20 loops=1)

3. 0.049 0.049 ↑ 2,596.1 20 1

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

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 12
4. 0.140 389.720 ↑ 1.0 1 20

Aggregate (cost=5,577.26..5,577.27 rows=1 width=8) (actual time=19.486..19.486 rows=1 loops=20)

5. 82.265 389.580 ↑ 1,221.0 1 20

Hash Join (cost=0.95..5,577.26 rows=1,221 width=0) (actual time=11.017..19.479 rows=1 loops=20)

  • Hash Cond: ((appuseraccount.account)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
6. 79.214 307.055 ↑ 1.2 104,686 7

Append (cost=0.00..3,885.15 rows=122,106 width=154) (actual time=0.004..43.865 rows=104,686 loops=7)

7. 217.833 217.833 ↑ 1.2 101,364 7

Seq Scan on appuseraccount (cost=0.00..1,999.77 rows=118,267 width=71) (actual time=0.003..31.119 rows=101,364 loops=7)

8. 3.330 10.008 ↓ 1.0 3,875 6

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

9. 6.678 6.678 ↓ 1.0 3,875 6

Seq Scan on bluekeyuserinfo (cost=0.00..53.79 rows=3,839 width=104) (actual time=0.006..1.113 rows=3,875 loops=6)

10. 0.040 0.260 ↑ 2.0 1 20

Hash (cost=0.92..0.92 rows=2 width=24) (actual time=0.013..0.013 rows=1 loops=20)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.220 0.220 ↑ 2.0 1 20

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.92 rows=2 width=24) (actual time=0.010..0.011 rows=1 loops=20)

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

SubPlan (for Nested Loop)

13. 0.100 0.480 ↑ 1.0 1 20

Aggregate (cost=6.29..6.30 rows=1 width=32) (actual time=0.024..0.024 rows=1 loops=20)

14. 0.380 0.380 ↑ 4.8 10 20

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

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
15. 0.160 103.720 ↑ 1.0 1 20

Aggregate (cost=0.68..0.69 rows=1 width=8) (actual time=5.186..5.186 rows=1 loops=20)

16. 103.560 103.560 ↓ 0.0 0 20

Index Scan using opendoorrecord_opentime_idx on opendoorrecord opendoorrecord_1 (cost=0.46..0.68 rows=1 width=20) (actual time=5.032..5.178 rows=0 loops=20)

  • Index Cond: (((opentime)::text > ((((timezone('PRC'::text, now()) - '1 day'::interval))::timestamp(0) without time zone)::character varying)::text) AND ((opentime)::text < (((timezone('PRC'::text, now()))::timestamp(0) without time zone)::character varying)::text))
  • Filter: ((houseid)::text = (houseinfo.houseid)::text)
  • Rows Removed by Filter: 13647
Planning time : 1.004 ms
Execution time : 494.221 ms