explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RnYw

Settings
# exclusive inclusive rows x rows loops node
1. 0.670 5,947.414 ↑ 1.0 122 1

Nested Loop (cost=136,807,338.96..304,792,439.86 rows=122 width=177) (actual time=3,996.883..5,947.414 rows=122 loops=1)

2.          

CTE euser

3. 30.354 3,917.574 ↑ 597.4 103,228 1

Nested Loop (cost=26,734.38..135,419,032.64 rows=61,667,864 width=130) (actual time=347.240..3,917.574 rows=103,228 loops=1)

4. 86.184 480.696 ↑ 597.4 103,228 1

Merge Join (cost=26,732.23..952,255.18 rows=61,667,864 width=156) (actual time=347.203..480.696 rows=103,228 loops=1)

  • Merge Cond: (((COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text) = (all_user.account)::text)
5. 121.095 219.353 ↓ 1.0 104,542 1

Sort (cost=11,307.83..11,560.33 rows=100,998 width=58) (actual time=192.727..219.353 rows=104,542 loops=1)

  • Sort Key: ((COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
  • Sort Method: quicksort Memory: 17750kB
6. 98.258 98.258 ↓ 1.0 104,542 1

Index Scan using idx_index_expi_lockkeyinf_1578488597_13 on lockkeyinfo (cost=0.43..2,912.89 rows=100,998 width=58) (actual time=0.017..98.258 rows=104,542 loops=1)

  • Index Cond: ((expireddate)::text > (timezone('PRC'::text, now()))::text)
  • Filter: (keystate = 0)
  • Rows Removed by Filter: 10066
7. 107.444 175.159 ↓ 1.3 164,716 1

Sort (cost=15,424.40..15,729.69 rows=122,117 width=122) (actual time=154.462..175.159 rows=164,716 loops=1)

  • Sort Key: all_user.account
  • Sort Method: quicksort Memory: 12546kB
8. 16.487 67.715 ↓ 1.0 122,153 1

Subquery Scan on all_user (cost=0.00..5,106.80 rows=122,117 width=122) (actual time=0.019..67.715 rows=122,153 loops=1)

9. 12.787 51.228 ↓ 1.0 122,153 1

Append (cost=0.00..3,885.62 rows=122,117 width=154) (actual time=0.017..51.228 rows=122,153 loops=1)

10. 36.582 36.582 ↑ 1.0 118,278 1

Seq Scan on appuseraccount (cost=0.00..2,000.08 rows=118,278 width=71) (actual time=0.016..36.582 rows=118,278 loops=1)

11. 0.631 1.859 ↓ 1.0 3,875 1

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

12. 1.228 1.228 ↓ 1.0 3,875 1

Seq Scan on bluekeyuserinfo (cost=0.00..53.79 rows=3,839 width=104) (actual time=0.010..1.228 rows=3,875 loops=1)

13. 412.912 3,406.524 ↑ 1.0 1 103,228

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual time=0.033..0.033 rows=1 loops=103,228)

14. 1,445.192 2,993.612 ↓ 9.0 9 103,228

Bitmap Heap Scan on opendoorrecord opendoorrecord_1 (cost=2.03..2.15 rows=1 width=20) (actual time=0.018..0.029 rows=9 loops=103,228)

  • Recheck Cond: (((userid)::text = (all_user.account)::text) AND ((houseid)::text = (lockkeyinfo.houseid)::text))
  • Heap Blocks: exact=909599
15. 190.928 1,548.420 ↓ 0.0 0 103,228

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=103,228)

16. 722.596 722.596 ↓ 1.1 53 103,228

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual time=0.007..0.007 rows=53 loops=103,228)

  • Index Cond: ((userid)::text = (all_user.account)::text)
17. 634.896 634.896 ↑ 1.4 34 79,362

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual time=0.008..0.008 rows=34 loops=79,362)

  • Index Cond: ((houseid)::text = (lockkeyinfo.houseid)::text)
18. 0.462 14.372 ↑ 1.0 122 1

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

19. 0.658 3.174 ↑ 1.0 122 1

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

20. 0.198 0.198 ↑ 1.0 122 1

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

21. 2.318 2.318 ↑ 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.019..0.019 rows=1 loops=122)

  • Index Cond: ((houseid)::text = _regulated_house_alpub.houseid)
22. 4.514 10.736 ↑ 1.0 1 122

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

23. 6.222 6.222 ↑ 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.051 rows=27 loops=122)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
24. 5.124 5,932.372 ↑ 1.0 1 122

Aggregate (cost=1,388,297.79..1,388,297.80 rows=1 width=32) (actual time=48.625..48.626 rows=1 loops=122)

25. 5,927.248 5,927.248 ↑ 102,779.7 3 122

CTE Scan on euser (cost=0.00..1,387,526.94 rows=308,339 width=24) (actual time=7.303..48.584 rows=3 loops=122)

  • Filter: (_regulated_house_alpub.houseid = (houseid)::text)
  • Rows Removed by Filter: 103225
Planning time : 0.801 ms
Execution time : 5,952.788 ms