explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f2WI

Settings
# exclusive inclusive rows x rows loops node
1. 14.238 2,086.118 ↑ 25.0 6,464 1

Hash Join (cost=165,948.33..438,527.29 rows=161,923 width=404) (actual time=1,493.666..2,086.118 rows=6,464 loops=1)

  • Hash Cond: ((lockinfo_1.houseid)::text = (houseinfo.houseid)::text)
2. 6.946 645.818 ↑ 50.7 6,490 1

Nested Loop (cost=2,415.61..271,332.28 rows=328,918 width=263) (actual time=67.585..645.818 rows=6,490 loops=1)

3. 8.334 80.732 ↑ 50.7 6,490 1

Merge Join (cost=2,414.84..7,375.59 rows=328,918 width=250) (actual time=67.539..80.732 rows=6,490 loops=1)

  • Merge Cond: (((COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text) = (appuseraccount.account)::text)
4. 9.760 50.683 ↓ 1.2 6,582 1

Sort (cost=1,089.28..1,102.57 rows=5,315 width=120) (actual time=49.048..50.683 rows=6,582 loops=1)

  • Sort Key: ((COALESCE(lockkeyinfo.keyholder, lockkeyinfo.useraccount))::text)
  • Sort Method: quicksort Memory: 1941kB
5. 4.757 40.923 ↓ 1.2 6,582 1

Hash Join (cost=267.75..760.39 rows=5,315 width=120) (actual time=10.232..40.923 rows=6,582 loops=1)

  • Hash Cond: ((lockinfo_1.houseid)::text = (houseinfo_1.houseid)::text)
6. 6.734 29.739 ↓ 1.1 8,216 1

Hash Join (cost=117.14..589.99 rows=7,536 width=87) (actual time=3.793..29.739 rows=8,216 loops=1)

  • Hash Cond: ((lockkeyinfo.lockid)::text = (lockinfo_1.lockid)::text)
7. 19.249 19.249 ↓ 1.1 8,216 1

Seq Scan on lockkeyinfo (cost=0.00..453.05 rows=7,536 width=64) (actual time=0.020..19.249 rows=8,216 loops=1)

  • Filter: ((keystate = 0) AND ((expireddate)::text > (timezone('PRC'::text, now()))::text))
  • Rows Removed by Filter: 6820
8. 1.530 3.756 ↑ 1.0 4,516 1

Hash (cost=60.56..60.56 rows=4,526 width=43) (actual time=3.756..3.756 rows=4,516 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 403kB
9. 2.226 2.226 ↑ 1.0 4,516 1

Seq Scan on lockinfo lockinfo_1 (cost=0.00..60.56 rows=4,526 width=43) (actual time=0.006..2.226 rows=4,516 loops=1)

10. 2.318 6.427 ↑ 1.0 4,043 1

Hash (cost=98.99..98.99 rows=4,130 width=33) (actual time=6.427..6.427 rows=4,043 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 321kB
11. 4.109 4.109 ↑ 1.0 4,043 1

Seq Scan on houseinfo houseinfo_1 (cost=0.00..98.99 rows=4,130 width=33) (actual time=0.010..4.109 rows=4,043 loops=1)

  • Filter: ((housecategory)::text = '2'::text)
  • Rows Removed by Filter: 1797
12. 14.084 21.715 ↓ 1.2 15,382 1

Sort (cost=1,325.55..1,356.50 rows=12,378 width=154) (actual time=18.480..21.715 rows=15,382 loops=1)

  • Sort Key: appuseraccount.account
  • Sort Method: quicksort Memory: 1233kB
13. 1.854 7.631 ↑ 1.0 12,369 1

Append (cost=0.00..360.35 rows=12,378 width=154) (actual time=0.012..7.631 rows=12,369 loops=1)

14. 5.709 5.709 ↑ 1.0 12,276 1

Seq Scan on appuseraccount (cost=0.00..173.27 rows=12,277 width=71) (actual time=0.011..5.709 rows=12,276 loops=1)

15. 0.021 0.068 ↑ 1.1 93 1

Subquery Scan on *SELECT* 2 (cost=0.00..2.42 rows=101 width=166) (actual time=0.010..0.068 rows=93 loops=1)

16. 0.047 0.047 ↑ 1.1 93 1

Seq Scan on bluekeyuserinfo (cost=0.00..1.41 rows=101 width=166) (actual time=0.009..0.047 rows=93 loops=1)

17. 6.490 558.140 ↑ 1.0 1 6,490

Result (cost=0.77..0.78 rows=1 width=32) (actual time=0.086..0.086 rows=1 loops=6,490)

18.          

Initplan (for Result)

19. 6.490 551.650 ↑ 1.0 1 6,490

Limit (cost=0.55..0.77 rows=1 width=32) (actual time=0.085..0.085 rows=1 loops=6,490)

20. 545.160 545.160 ↑ 1.0 1 6,490

Index Only Scan Backward using idx_idx_userid_opendoorre_1578488409_1 on opendoorrecord opendoorrecord_1 (cost=0.55..0.77 rows=1 width=32) (actual time=0.084..0.084 rows=1 loops=6,490)

  • Index Cond: ((userid = ($2)::text) AND (lockid = ($1)::text) AND (opentime IS NOT NULL))
  • Heap Fetches: 5003
21. 3.687 1,426.062 ↑ 1.3 2,217 1

Hash (cost=163,496.69..163,496.69 rows=2,883 width=208) (actual time=1,426.062..1,426.062 rows=2,217 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 556kB
22. 1.727 1,422.375 ↑ 1.3 2,217 1

Merge Left Join (cost=419.85..163,496.69 rows=2,883 width=208) (actual time=11.976..1,422.375 rows=2,217 loops=1)

  • Merge Cond: ((houseinfo.houseid)::text = _regulated_house_alemp.houseid)
23. 3.318 1,420.636 ↑ 1.3 2,217 1

Nested Loop (cost=419.70..163,461.22 rows=2,883 width=200) (actual time=11.967..1,420.636 rows=2,217 loops=1)

24. 4.241 20.608 ↑ 1.3 2,217 1

Merge Join (cost=363.20..481.69 rows=2,883 width=179) (actual time=11.381..20.608 rows=2,217 loops=1)

  • Merge Cond: ((lockinfo.houseid)::text = (houseinfo.houseid)::text)
25. 4.942 4.942 ↑ 1.0 4,516 1

Index Scan using idx_index_hous_lockinfo_1575546514_2 on lockinfo (cost=0.28..87.97 rows=4,526 width=43) (actual time=0.009..4.942 rows=4,516 loops=1)

26. 5.479 11.425 ↓ 1.1 4,012 1

Sort (cost=334.94..344.26 rows=3,731 width=136) (actual time=10.524..11.425 rows=4,012 loops=1)

  • Sort Key: houseinfo.houseid
  • Sort Method: quicksort Memory: 974kB
27. 2.346 5.946 ↓ 1.1 4,010 1

Hash Join (cost=3.45..113.59 rows=3,731 width=136) (actual time=0.131..5.946 rows=4,010 loops=1)

  • Hash Cond: ((houseinfo.customid)::text = (custominfo.customid)::text)
28. 3.486 3.486 ↑ 1.0 4,043 1

Seq Scan on houseinfo (cost=0.00..98.99 rows=4,130 width=140) (actual time=0.010..3.486 rows=4,043 loops=1)

  • Filter: ((housecategory)::text = '2'::text)
  • Rows Removed by Filter: 1797
29. 0.058 0.114 ↓ 1.2 155 1

Hash (cost=1.81..1.81 rows=131 width=61) (actual time=0.114..0.114 rows=155 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
30. 0.056 0.056 ↓ 1.2 155 1

Seq Scan on custominfo (cost=0.00..1.81 rows=131 width=61) (actual time=0.006..0.056 rows=155 loops=1)

31. 705.006 1,396.710 ↑ 1.0 1 2,217

Aggregate (cost=56.50..56.51 rows=1 width=40) (actual time=0.630..0.630 rows=1 loops=2,217)

32. 691.704 691.704 ↑ 1.3 286 2,217

Index Only Scan using opendoorrecord_lockid_opentime_idx on opendoorrecord (cost=0.42..42.89 rows=363 width=20) (actual time=0.011..0.312 rows=286 loops=2,217)

  • Index Cond: (lockid = (lockinfo.lockid)::text)
  • Heap Fetches: 634294
33. 0.012 0.012 ↑ 200.0 6 1

Index Scan using _regulated_house_alemp_pkey on _regulated_house_alemp (cost=0.15..19.35 rows=1,200 width=40) (actual time=0.006..0.012 rows=6 loops=1)

Planning time : 3.494 ms
Execution time : 2,087.152 ms