explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BgR

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 1,080.475 ↑ 1.0 20 1

Limit (cost=8,227.96..172,775.70 rows=20 width=178) (actual time=63.605..1,080.475 rows=20 loops=1)

2. 0.101 1,080.455 ↑ 3,891.5 20 1

Merge Left Join (cost=8,227.96..640,345,750.96 rows=77,830 width=178) (actual time=63.604..1,080.455 rows=20 loops=1)

  • Merge Cond: ((houseinfo.houseid)::text = _regulated_house_alpub.houseid)
3. 0.064 1,080.344 ↑ 3,891.5 20 1

Nested Loop (cost=8,227.82..640,345,163.34 rows=77,830 width=218) (actual time=63.587..1,080.344 rows=20 loops=1)

4. 0.064 1.620 ↑ 3,891.5 20 1

Nested Loop (cost=8.54..636,227.18 rows=77,830 width=186) (actual time=0.251..1.620 rows=20 loops=1)

5. 0.056 0.056 ↑ 3,891.5 20 1

Index Scan using "pk_houseinfo_1570385679_119 " on houseinfo (cost=0.42..1,732.47 rows=77,830 width=146) (actual time=0.008..0.056 rows=20 loops=1)

6. 0.680 1.500 ↑ 1.0 1 20

Aggregate (cost=8.12..8.13 rows=1 width=40) (actual time=0.074..0.075 rows=1 loops=20)

7. 0.820 0.820 ↑ 1.9 26 20

Index Scan using opendoorrecord_houseid_idx on opendoorrecord (cost=0.43..6.28 rows=49 width=20) (actual time=0.009..0.041 rows=26 loops=20)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
8. 0.686 1,078.660 ↑ 1.0 1 20

Aggregate (cost=8,219.28..8,219.29 rows=1 width=32) (actual time=53.933..53.933 rows=1 loops=20)

9. 227.939 1,075.480 ↑ 610.5 2 20

Hash Join (cost=0.94..5,578.25 rows=1,221 width=154) (actual time=14.207..53.774 rows=2 loops=20)

  • Hash Cond: ((appuseraccount.account)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
10. 218.960 847.161 ↓ 1.0 122,160 17

Append (cost=0.00..3,885.90 rows=122,124 width=154) (actual time=0.003..49.833 rows=122,160 loops=17)

11. 600.134 600.134 ↑ 1.0 118,285 17

Seq Scan on appuseraccount (cost=0.00..2,000.25 rows=118,285 width=71) (actual time=0.003..35.302 rows=118,285 loops=17)

12. 9.452 28.067 ↓ 1.0 3,875 17

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

13. 18.615 18.615 ↓ 1.0 3,875 17

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

14. 0.060 0.380 ↑ 1.0 2 20

Hash (cost=0.91..0.91 rows=2 width=24) (actual time=0.019..0.019 rows=2 loops=20)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.320 0.320 ↑ 1.0 2 20

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.91 rows=2 width=24) (actual time=0.012..0.016 rows=2 loops=20)

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

SubPlan (for Aggregate)

17. 0.387 2.494 ↑ 1.0 1 43

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual time=0.058..0.058 rows=1 loops=43)

18. 0.860 2.107 ↓ 11.0 11 43

Bitmap Heap Scan on opendoorrecord opendoorrecord_1 (cost=2.03..2.15 rows=1 width=20) (actual time=0.034..0.049 rows=11 loops=43)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = ($0)::text))
  • Heap Blocks: exact=470
19. 0.168 1.247 ↓ 0.0 0 43

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.029..0.029 rows=0 loops=43)

20. 0.731 0.731 ↓ 1.4 68 43

Bitmap Index Scan on idx_openrecord_opendoorre_1578489340_20 (cost=0.00..0.89 rows=48 width=0) (actual time=0.017..0.017 rows=68 loops=43)

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
21. 0.348 0.348 ↓ 1.3 64 29

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual time=0.012..0.012 rows=64 loops=29)

  • Index Cond: ((houseid)::text = ($0)::text)
22. 0.010 0.010 ↑ 122.0 1 1

Index Scan using _regulated_house_alpub_pkey on _regulated_house_alpub (cost=0.14..2.37 rows=122 width=41) (actual time=0.010..0.010 rows=1 loops=1)

Planning time : 0.955 ms
Execution time : 1,080.605 ms