explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3rMF

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 2,253.265 ↑ 1.0 20 1

Limit (cost=9,077.29..190,760.56 rows=20 width=138) (actual time=92.618..2,253.265 rows=20 loops=1)

2. 0.279 2,253.243 ↑ 2,596.1 20 1

Nested Loop (cost=9,077.29..471,677,009.58 rows=51,922 width=138) (actual time=92.617..2,253.243 rows=20 loops=1)

3. 0.064 0.064 ↑ 2,596.1 20 1

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

  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
  • Rows Removed by Filter: 12
4. 0.240 2,141.200 ↑ 1.0 1 20

Aggregate (cost=9,077.29..9,077.30 rows=1 width=8) (actual time=107.060..107.060 rows=1 loops=20)

5. 90.034 2,140.960 ↑ 1,221.0 1 20

Hash Join (cost=3,500.97..9,077.29 rows=1,221 width=0) (actual time=98.039..107.048 rows=1 loops=20)

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

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

7. 237.524 237.524 ↑ 1.2 101,364 7

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

8. 3.642 10.956 ↓ 1.0 3,875 6

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

9. 7.314 7.314 ↓ 1.0 3,875 6

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

10. 0.160 1,719.000 ↑ 2.0 1 20

Hash (cost=3,500.95..3,500.95 rows=2 width=24) (actual time=85.950..85.950 rows=1 loops=20)

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

Index Scan using idx_index_expi_lockkeyinf_1578488597_13 on lockkeyinfo (cost=0.43..3,500.95 rows=2 width=24) (actual time=70.009..85.942 rows=1 loops=20)

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

SubPlan (for Nested Loop)

13. 0.140 0.660 ↑ 1.0 1 20

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

14. 0.520 0.520 ↑ 4.8 10 20

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

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

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

16. 110.780 110.780 ↓ 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.383..5.539 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: 13621
Planning time : 0.871 ms
Execution time : 2,253.373 ms