explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vfOi

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 2.752 ↑ 1.0 20 1

Limit (cost=14.78..312.32 rows=20 width=178) (actual time=0.074..2.752 rows=20 loops=1)

2. 0.040 2.745 ↑ 3,891.5 20 1

Nested Loop Left Join (cost=14.78..1,157,884.18 rows=77,830 width=178) (actual time=0.073..2.745 rows=20 loops=1)

3. 0.025 2.685 ↑ 3,891.5 20 1

Nested Loop (cost=14.64..1,145,041.85 rows=77,830 width=218) (actual time=0.067..2.685 rows=20 loops=1)

4. 0.031 0.840 ↑ 3,891.5 20 1

Nested Loop (cost=8.12..635,761.01 rows=77,830 width=186) (actual time=0.021..0.840 rows=20 loops=1)

5. 0.009 0.009 ↑ 3,891.5 20 1

Seq Scan on houseinfo (cost=0.00..1,266.30 rows=77,830 width=146) (actual time=0.004..0.009 rows=20 loops=1)

6. 0.340 0.800 ↑ 1.0 1 20

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

7. 0.460 0.460 ↑ 3.1 16 20

Index Scan using opendoorrecord_houseid_idx on opendoorrecord (cost=0.43..6.28 rows=49 width=20) (actual time=0.005..0.023 rows=16 loops=20)

  • Index Cond: ((houseid)::text = (houseinfo.houseid)::text)
8. 0.140 1.820 ↑ 1.0 1 20

Aggregate (cost=6.51..6.52 rows=1 width=32) (actual time=0.091..0.091 rows=1 loops=20)

9. 0.040 0.420 ↑ 1.0 2 20

Nested Loop (cost=0.83..2.19 rows=2 width=39) (actual time=0.011..0.021 rows=2 loops=20)

10. 0.200 0.200 ↑ 1.0 2 20

Index Scan using lockkeyinfo_houseid_idx on lockkeyinfo (cost=0.42..0.91 rows=2 width=24) (actual time=0.006..0.010 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: 2
11. 0.180 0.180 ↑ 1.0 1 36

Index Scan using appuseraccount_account_idx on appuseraccount (cost=0.42..0.64 rows=1 width=39) (actual time=0.005..0.005 rows=1 loops=36)

  • Index Cond: ((account)::text = (COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
12.          

SubPlan (for Aggregate)

13. 0.105 1.260 ↑ 1.0 1 35

Aggregate (cost=2.15..2.16 rows=1 width=32) (actual time=0.036..0.036 rows=1 loops=35)

14. 0.280 1.155 ↓ 8.0 8 35

Bitmap Heap Scan on opendoorrecord opendoorrecord_1 (cost=2.03..2.15 rows=1 width=20) (actual time=0.027..0.033 rows=8 loops=35)

  • Recheck Cond: (((userid)::text = (appuseraccount.account)::text) AND ((houseid)::text = ($0)::text))
  • Heap Blocks: exact=285
15. 0.070 0.875 ↓ 0.0 0 35

BitmapAnd (cost=2.03..2.03 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=35)

16. 0.665 0.665 ↓ 4.0 194 35

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=194 loops=35)

  • Index Cond: ((userid)::text = (appuseraccount.account)::text)
17. 0.140 0.140 ↑ 1.4 35 28

Bitmap Index Scan on opendoorrecord_houseid_idx (cost=0.00..0.90 rows=49 width=0) (actual time=0.005..0.005 rows=35 loops=28)

  • Index Cond: ((houseid)::text = ($0)::text)
18. 0.020 0.020 ↓ 0.0 0 20

Index Scan using _regulated_house_alpub_pkey on _regulated_house_alpub (cost=0.14..0.16 rows=1 width=41) (actual time=0.001..0.001 rows=0 loops=20)

  • Index Cond: ((houseinfo.houseid)::text = houseid)
Planning time : 0.866 ms
Execution time : 2.859 ms