explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gyaa

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 2,548.125 ↓ 5.0 10 1

Limit (cost=96,455.31..96,455.32 rows=2 width=150) (actual time=2,548.124..2,548.125 rows=10 loops=1)

2. 15.959 2,548.123 ↓ 5.0 10 1

Sort (cost=96,455.31..96,455.32 rows=2 width=150) (actual time=2,548.122..2,548.123 rows=10 loops=1)

  • Sort Key: odr.opentime DESC
  • Sort Method: top-N heapsort Memory: 28kB
3. 7.989 2,532.164 ↓ 40,566.0 81,132 1

Append (cost=59,469.59..96,455.30 rows=2 width=150) (actual time=2,100.348..2,532.164 rows=81,132 loops=1)

4. 36.456 2,153.520 ↓ 74,484.0 74,484 1

Unique (cost=59,469.59..59,469.61 rows=1 width=189) (actual time=2,100.347..2,153.520 rows=74,484 loops=1)

5. 160.264 2,117.064 ↓ 121,128.0 121,128 1

Sort (cost=59,469.59..59,469.60 rows=1 width=189) (actual time=2,100.344..2,117.064 rows=121,128 loops=1)

  • Sort Key: (COALESCE(ac.username, bk.username)), (COALESCE(ac.account, bk.keyholder)), hi.detailaddr, hi.housenumber, odr.opentime
  • Sort Method: quicksort Memory: 33,007kB
6. 155.249 1,956.800 ↓ 121,128.0 121,128 1

Hash Join (cost=51,461.54..59,469.58 rows=1 width=189) (actual time=1,761.059..1,956.800 rows=121,128 loops=1)

  • Hash Cond: (((odr.lockid)::text = (lk.lockid)::text) AND ((odr.userid)::text = ((COALESCE(lk.keyholder, lk.useraccount)))::text))
7. 40.997 40.997 ↓ 1,756.7 82,563 1

Index Scan using opendoorrecord_opentime_idx on opendoorrecord odr (cost=0.58..1.95 rows=47 width=51) (actual time=0.027..40.997 rows=82,563 loops=1)

  • Index Cond: (((opentime)::text >= (date_trunc('day'::text, timezone('PRC'::text, now())))::text) AND ((opentime)::text <= (timezone('PRC'::text, now()))::text))
8. 314.320 1,760.554 ↑ 1.0 516,763 1

Hash (cost=41,693.85..41,693.85 rows=541,794 width=217) (actual time=1,760.554..1,760.554 rows=516,763 loops=1)

  • Buckets: 524,288 Batches: 2 Memory Usage: 52,842kB
9. 179.279 1,446.234 ↑ 1.0 516,763 1

Hash Left Join (cost=16,030.87..41,693.85 rows=541,794 width=217) (actual time=365.055..1,446.234 rows=516,763 loops=1)

  • Hash Cond: (((lk.lockid)::text = (bk.lockid)::text) AND (((COALESCE(lk.keyholder, lk.useraccount)))::text = (bk.bluekeyaccount)::text))
10. 280.455 1,263.871 ↑ 1.0 516,763 1

Hash Left Join (cost=15,862.32..38,680.88 rows=541,794 width=196) (actual time=361.956..1,263.871 rows=516,763 loops=1)

  • Hash Cond: (((COALESCE(lk.keyholder, lk.useraccount)))::text = (ac.account)::text)
11. 294.781 862.672 ↑ 1.0 516,763 1

Hash Join (cost=8,158.72..29,555.05 rows=541,794 width=175) (actual time=241.058..862.672 rows=516,763 loops=1)

  • Hash Cond: ((lk.lockid)::text = (li.lockid)::text)
12. 53.950 326.942 ↓ 1.0 543,158 1

Append (cost=0.00..13,944.57 rows=542,353 width=77) (actual time=0.012..326.942 rows=543,158 loops=1)

13. 98.757 98.757 ↓ 1.0 148,934 1

Seq Scan on lockkeyinfo lk (cost=0.00..4,138.06 rows=148,698 width=77) (actual time=0.011..98.757 rows=148,934 loops=1)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 92,993
14. 174.235 174.235 ↓ 1.0 394,224 1

Seq Scan on keyhistoryinfo hk (cost=0.00..7,094.74 rows=393,655 width=77) (actual time=0.009..174.235 rows=394,224 loops=1)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 26,998
15. 28.087 240.949 ↑ 1.1 93,625 1

Hash (cost=6,885.53..6,885.53 rows=101,855 width=98) (actual time=240.949..240.949 rows=93,625 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 13,069kB
16. 31.854 212.862 ↑ 1.1 93,625 1

Hash Left Join (cost=3,816.28..6,885.53 rows=101,855 width=98) (actual time=99.520..212.862 rows=93,625 loops=1)

  • Hash Cond: ((hi.xzqhdm)::text = xz.""代码"")
17. 65.552 179.635 ↑ 1.1 93,625 1

Hash Join (cost=3,738.73..5,411.49 rows=101,855 width=110) (actual time=98.120..179.635 rows=93,625 loops=1)

  • Hash Cond: ((li.houseid)::text = (hi.houseid)::text)
18. 16.161 16.161 ↑ 1.0 97,789 1

Seq Scan on lockinfo li (cost=0.00..1,405.10 rows=101,960 width=52) (actual time=0.005..16.161 rows=97,789 loops=1)

19. 42.486 97.922 ↑ 1.0 129,672 1

Hash (cost=2,089.88..2,089.88 rows=131,908 width=126) (actual time=97.921..97.922 rows=129,672 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 22,344kB
20. 55.436 55.436 ↑ 1.0 129,672 1

Seq Scan on houseinfo hi (cost=0.00..2,089.88 rows=131,908 width=126) (actual time=0.007..55.436 rows=129,672 loops=1)

21. 0.289 1.373 ↓ 1.0 1,639 1

Hash (cost=57.15..57.15 rows=1,632 width=12) (actual time=1.373..1.373 rows=1,639 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
22. 0.442 1.084 ↓ 1.0 1,639 1

Hash Right Join (cost=39.02..57.15 rows=1,632 width=12) (actual time=0.524..1.084 rows=1,639 loops=1)

  • Hash Cond: (ys.""行政区划_id"" = xz.id)
23. 0.128 0.128 ↓ 1.0 1,334 1

Seq Scan on ""行政区划与管理单位代码映射"" ys (cost=0.00..14.62 rows=1,332 width=32) (actual time=0.003..0.128 rows=1,334 loops=1)

24. 0.275 0.514 ↑ 1.0 1,632 1

Hash (cost=18.62..18.62 rows=1,632 width=28) (actual time=0.514..0.514 rows=1,632 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 113kB
25. 0.239 0.239 ↑ 1.0 1,632 1

Seq Scan on ""行政区划代码"" xz (cost=0.00..18.62 rows=1,632 width=28) (actual time=0.009..0.239 rows=1,632 loops=1)

26. 55.703 120.744 ↓ 1.0 260,853 1

Hash (cost=4,445.10..4,445.10 rows=260,680 width=21) (actual time=120.743..120.744 rows=260,853 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 15,550kB
27. 65.041 65.041 ↓ 1.0 260,853 1

Seq Scan on appuseraccount ac (cost=0.00..4,445.10 rows=260,680 width=21) (actual time=0.006..65.041 rows=260,853 loops=1)

28. 1.630 3.084 ↑ 1.0 5,846 1

Hash (cost=79.60..79.60 rows=5,930 width=59) (actual time=3.084..3.084 rows=5,846 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 546kB
29. 1.454 1.454 ↑ 1.0 5,846 1

Seq Scan on bluekeyuserinfo bk (cost=0.00..79.60 rows=5,930 width=59) (actual time=0.008..1.454 rows=5,846 loops=1)

30. 2.432 370.655 ↓ 6,648.0 6,648 1

Unique (cost=36,985.65..36,985.66 rows=1 width=137) (actual time=367.673..370.655 rows=6,648 loops=1)

31. 9.279 368.223 ↓ 7,864.0 7,864 1

Sort (cost=36,985.65..36,985.65 rows=1 width=137) (actual time=367.671..368.223 rows=7,864 loops=1)

  • Sort Key: (COALESCE(ac_1.username, bk_1.username)), (COALESCE(ac_1.account, bk_1.keyholder)), hi_1.detailaddr, hi_1.housenumber, odr_1.opentime
  • Sort Method: quicksort Memory: 1,517kB
32. 12.178 358.944 ↓ 7,864.0 7,864 1

Hash Join (cost=33,936.27..36,985.64 rows=1 width=137) (actual time=224.556..358.944 rows=7,864 loops=1)

  • Hash Cond: (((lk_1.lockid)::text = (odr_1.lockid)::text) AND (((COALESCE(lk_1.keyholder, lk_1.useraccount)))::text = (odr_1.userid)::text))
33. 13.684 155.001 ↑ 1.1 50,081 1

Hash Left Join (cost=1,678.98..4,327.13 rows=53,494 width=279) (actual time=32.765..155.001 rows=50,081 loops=1)

  • Hash Cond: (((lk_1.lockid)::text = (bk_1.lockid)::text) AND (((COALESCE(lk_1.keyholder, lk_1.useraccount)))::text = (bk_1.bluekeyaccount)::text))
34. 21.557 141.213 ↑ 1.1 50,081 1

Hash Left Join (cost=1,673.71..4,041.00 rows=53,494 width=170) (actual time=32.650..141.213 rows=50,081 loops=1)

  • Hash Cond: (((COALESCE(lk_1.keyholder, lk_1.useraccount)))::text = (ac_1.account)::text)
35. 15.588 100.976 ↑ 1.1 50,081 1

Hash Left Join (cost=645.87..2,872.73 rows=53,494 width=149) (actual time=13.797..100.976 rows=50,081 loops=1)

  • Hash Cond: ((hi_1.xzqhdm)::text = xz_1.""代码"")
36. 20.680 83.965 ↑ 1.1 50,081 1

Hash Join (cost=568.33..2,270.02 rows=53,494 width=161) (actual time=12.368..83.965 rows=50,081 loops=1)

  • Hash Cond: ((li_1.houseid)::text = (hi_1.houseid)::text)
37. 18.662 55.516 ↑ 1.0 52,199 1

Hash Join (cost=247.82..1,809.03 rows=53,500 width=124) (actual time=4.584..55.516 rows=52,199 loops=1)

  • Hash Cond: ((lk_1.lockid)::text = (li_1.lockid)::text)
38. 5.210 32.296 ↑ 1.0 53,439 1

Append (cost=0.00..1,420.70 rows=53,500 width=77) (actual time=0.009..32.296 rows=53,439 loops=1)

39. 17.215 17.215 ↑ 1.0 35,289 1

Seq Scan on lockkeyinfo lk_1 (cost=0.00..726.84 rows=35,666 width=77) (actual time=0.008..17.215 rows=35,289 loops=1)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 6,519
40. 9.871 9.871 ↓ 1.0 18,150 1

Seq Scan on keyhistoryinfo hk_1 (cost=0.00..426.36 rows=17,834 width=77) (actual time=0.010..9.871 rows=18,150 loops=1)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 6,862
41. 1.803 4.558 ↑ 1.0 9,503 1

Hash (cost=128.70..128.70 rows=9,530 width=47) (actual time=4.558..4.558 rows=9,503 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 873kB
42. 2.755 2.755 ↑ 1.0 9,503 1

Seq Scan on lockinfo li_1 (cost=0.00..128.70 rows=9,530 width=47) (actual time=0.005..2.755 rows=9,503 loops=1)

43. 2.985 7.769 ↓ 1.0 11,569 1

Hash (cost=177.50..177.50 rows=11,440 width=98) (actual time=7.769..7.769 rows=11,569 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,558kB
44. 4.784 4.784 ↓ 1.0 11,569 1

Seq Scan on houseinfo hi_1 (cost=0.00..177.50 rows=11,440 width=98) (actual time=0.016..4.784 rows=11,569 loops=1)

45. 0.279 1.423 ↓ 1.0 1,639 1

Hash (cost=57.15..57.15 rows=1,632 width=12) (actual time=1.422..1.423 rows=1,639 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
46. 0.478 1.144 ↓ 1.0 1,639 1

Hash Right Join (cost=39.02..57.15 rows=1,632 width=12) (actual time=0.543..1.144 rows=1,639 loops=1)

  • Hash Cond: (ys_1.""行政区划_id"" = xz_1.id)
47. 0.132 0.132 ↓ 1.0 1,334 1

Seq Scan on ""行政区划与管理单位代码映射"" ys_1 (cost=0.00..14.62 rows=1,332 width=32) (actual time=0.003..0.132 rows=1,334 loops=1)

48. 0.279 0.534 ↑ 1.0 1,632 1

Hash (cost=18.62..18.62 rows=1,632 width=28) (actual time=0.533..0.534 rows=1,632 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 113kB
49. 0.255 0.255 ↑ 1.0 1,632 1

Seq Scan on ""行政区划代码"" xz_1 (cost=0.00..18.62 rows=1,632 width=28) (actual time=0.007..0.255 rows=1,632 loops=1)

50. 8.324 18.680 ↑ 1.0 37,958 1

Hash (cost=552.87..552.87 rows=37,997 width=21) (actual time=18.680..18.680 rows=37,958 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,408kB
51. 10.356 10.356 ↑ 1.0 37,958 1

Seq Scan on appuseraccount ac_1 (cost=0.00..552.87 rows=37,997 width=21) (actual time=0.006..10.356 rows=37,958 loops=1)

52. 0.053 0.104 ↓ 1.0 183 1

Hash (cost=2.59..2.59 rows=179 width=148) (actual time=0.104..0.104 rows=183 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
53. 0.051 0.051 ↓ 1.0 183 1

Seq Scan on bluekeyuserinfo bk_1 (cost=0.00..2.59 rows=179 width=148) (actual time=0.008..0.051 rows=183 loops=1)

54. 2.916 191.765 ↓ 8,025.0 8,025 1

Hash (cost=32,257.28..32,257.28 rows=1 width=51) (actual time=191.765..191.765 rows=8,025 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 722kB
55. 188.849 188.849 ↓ 8,378.0 8,378 1

Index Scan using idx_openrecord_opendoorre_1596539491_83 on opendoorrecord odr_1 (cost=0.58..32,257.28 rows=1 width=51) (actual time=0.176..188.849 rows=8,378 loops=1)

  • Index Cond: (((opentime)::text >= (date_trunc('day'::text, timezone('PRC'::text, now())))::text) AND ((opentime)::text <= (timezone('PRC'::text, now()))::text))
Planning time : 5.102 ms
Execution time : 2,548.768 ms