explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jGVZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.846 4,129.358 ↑ 1.0 64 1

Seq Scan on _concern (cost=0.00..694,164.55 rows=64 width=129) (actual time=68.904..4,129.358 rows=64 loops=1)

  • Filter: (now() <@ validity)
2.          

SubPlan (for Seq Scan)

3. 0.960 4,128.512 ↑ 1.0 1 64

Aggregate (cost=10,846.27..10,846.28 rows=1 width=32) (actual time=64.508..64.508 rows=1 loops=64)

4. 0.192 4,127.552 ↑ 1.0 1 64

Nested Loop (cost=10,845.98..10,846.26 rows=1 width=218) (actual time=64.489..64.493 rows=1 loops=64)

5. 0.256 10.240 ↑ 1.0 1 64

Nested Loop (cost=8.38..8.63 rows=1 width=186) (actual time=0.157..0.160 rows=1 loops=64)

6. 1.024 1.024 ↑ 1.0 1 64

Index Scan using "pk_houseinfo_1570385679_119 " on houseinfo (cost=0.42..0.64 rows=1 width=146) (actual time=0.013..0.016 rows=1 loops=64)

  • Index Cond: ((houseid)::text = _concern.houseid)
  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
7. 2.624 8.960 ↑ 1.0 1 64

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

8. 6.336 6.336 ↑ 1.5 32 64

Index Scan using opendoorrecord_houseid_idx on opendoorrecord opendoorrecord_2 (cost=0.43..6.17 rows=48 width=20) (actual time=0.019..0.099 rows=32 loops=64)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
9. 3.448 4,117.120 ↑ 1.0 1 64

Aggregate (cost=10,837.60..10,837.61 rows=1 width=32) (actual time=64.329..64.330 rows=1 loops=64)

10. 859.904 4,100.032 ↑ 610.5 2 64

Hash Join (cost=0.95..5,576.92 rows=1,221 width=188) (actual time=23.225..64.063 rows=2 loops=64)

  • Hash Cond: ((appuseraccount.account)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
11. 822.592 3,238.528 ↓ 1.0 122,135 64

Append (cost=0.00..3,884.92 rows=122,097 width=154) (actual time=0.003..50.602 rows=122,135 loops=64)

12. 2,311.552 2,311.552 ↓ 1.0 118,260 64

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

13. 35.520 104.384 ↓ 1.0 3,875 64

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

14. 68.864 68.864 ↓ 1.0 3,875 64

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

15. 0.192 1.600 ↑ 1.0 2 64

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 1.408 1.408 ↑ 1.0 2 64

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

  • 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
17.          

SubPlan (for Aggregate)

18. 1.488 9.300 ↑ 1.0 1 124

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

19. 3.596 7.812 ↓ 16.0 16 124

Bitmap Heap Scan on opendoorrecord (cost=2.03..2.14 rows=1 width=20) (actual time=0.039..0.063 rows=16 loops=124)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = (lockkeyinfo.houseid)::text))
  • Heap Blocks: exact=1973
20. 0.650 4.216 ↓ 0.0 0 124

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

21. 2.356 2.356 ↑ 1.8 27 124

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

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
22. 1.210 1.210 ↑ 1.3 38 110

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

  • Index Cond: ((houseid)::text = (lockkeyinfo.houseid)::text)
23. 0.744 4.340 ↑ 1.0 1 124

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

24. 1.860 3.596 ↓ 16.0 16 124

Bitmap Heap Scan on opendoorrecord opendoorrecord_1 (cost=2.03..2.14 rows=1 width=20) (actual time=0.017..0.029 rows=16 loops=124)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = (lockkeyinfo.houseid)::text))
  • Heap Blocks: exact=1973
25. 0.332 1.736 ↓ 0.0 0 124

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

26. 0.744 0.744 ↑ 1.8 27 124

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

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
27. 0.660 0.660 ↑ 1.3 38 110

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

  • Index Cond: ((houseid)::text = (lockkeyinfo.houseid)::text)
Planning time : 1.275 ms
Execution time : 4,129.532 ms