explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DKxq

Settings
# exclusive inclusive rows x rows loops node
1. 0.104 454.044 ↑ 6.8 10 1

Seq Scan on _concern (cost=0.00..737,549.83 rows=68 width=129) (actual time=0.078..454.044 rows=10 loops=1)

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

SubPlan (for Seq Scan)

3. 0.130 453.940 ↑ 1.0 1 10

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

4. 0.020 453.810 ↑ 1.0 1 10

Nested Loop (cost=10,845.98..10,846.26 rows=1 width=218) (actual time=45.378..45.381 rows=1 loops=10)

5. 0.040 1.000 ↑ 1.0 1 10

Nested Loop (cost=8.38..8.63 rows=1 width=186) (actual time=0.097..0.100 rows=1 loops=10)

6. 0.140 0.140 ↑ 1.0 1 10

Index Scan using "pk_houseinfo_1570385679_119 " on houseinfo (cost=0.42..0.64 rows=1 width=146) (actual time=0.012..0.014 rows=1 loops=10)

  • Index Cond: ((houseid)::text = _concern.houseid)
  • Filter: ((xzqhdm)::text ~~ '330522%'::text)
7. 0.320 0.820 ↑ 1.0 1 10

Aggregate (cost=7.97..7.98 rows=1 width=40) (actual time=0.082..0.082 rows=1 loops=10)

8. 0.500 0.500 ↑ 1.8 27 10

Index Scan using opendoorrecord_houseid_idx on opendoorrecord opendoorrecord_2 (cost=0.43..6.17 rows=48 width=20) (actual time=0.009..0.050 rows=27 loops=10)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
9. 0.390 452.790 ↑ 1.0 1 10

Aggregate (cost=10,837.60..10,837.61 rows=1 width=32) (actual time=45.278..45.279 rows=1 loops=10)

10. 94.976 450.930 ↑ 610.5 2 10

Hash Join (cost=0.95..5,576.92 rows=1,221 width=188) (actual time=19.184..45.093 rows=2 loops=10)

  • Hash Cond: ((appuseraccount.account)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
11. 90.818 355.784 ↑ 1.1 106,868 8

Append (cost=0.00..3,884.92 rows=122,097 width=154) (actual time=0.003..44.473 rows=106,868 loops=8)

12. 253.304 253.304 ↑ 1.1 103,478 8

Seq Scan on appuseraccount (cost=0.00..1,999.68 rows=118,258 width=71) (actual time=0.003..31.663 rows=103,478 loops=8)

13. 3.976 11.662 ↓ 1.0 3,875 7

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

14. 7.686 7.686 ↓ 1.0 3,875 7

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

15. 0.020 0.170 ↑ 1.0 2 10

Hash (cost=0.92..0.92 rows=2 width=58) (actual time=0.017..0.017 rows=2 loops=10)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.150 0.150 ↑ 1.0 2 10

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.92 rows=2 width=58) (actual time=0.011..0.015 rows=2 loops=10)

  • 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. 0.165 0.975 ↑ 1.0 1 15

Aggregate (cost=2.14..2.15 rows=1 width=32) (actual time=0.065..0.065 rows=1 loops=15)

19. 0.420 0.810 ↓ 17.0 17 15

Bitmap Heap Scan on opendoorrecord (cost=2.03..2.14 rows=1 width=20) (actual time=0.031..0.054 rows=17 loops=15)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = (lockkeyinfo.houseid)::text))
  • Heap Blocks: exact=245
20. 0.059 0.390 ↓ 0.0 0 15

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=15)

21. 0.210 0.210 ↑ 1.8 26 15

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual time=0.014..0.014 rows=26 loops=15)

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
22. 0.121 0.121 ↑ 1.1 42 11

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

  • Index Cond: ((houseid)::text = (lockkeyinfo.houseid)::text)
23. 0.090 0.495 ↑ 1.0 1 15

Aggregate (cost=2.14..2.15 rows=1 width=32) (actual time=0.033..0.033 rows=1 loops=15)

24. 0.225 0.405 ↓ 17.0 17 15

Bitmap Heap Scan on opendoorrecord opendoorrecord_1 (cost=2.03..2.14 rows=1 width=20) (actual time=0.015..0.027 rows=17 loops=15)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = (lockkeyinfo.houseid)::text))
  • Heap Blocks: exact=245
25. 0.039 0.180 ↓ 0.0 0 15

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=15)

26. 0.075 0.075 ↑ 1.8 26 15

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual time=0.005..0.005 rows=26 loops=15)

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
27. 0.066 0.066 ↑ 1.1 42 11

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

  • Index Cond: ((houseid)::text = (lockkeyinfo.houseid)::text)
Planning time : 1.186 ms
Execution time : 454.173 ms