explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dbbq

Settings
# exclusive inclusive rows x rows loops node
1. 4,712.842 17,161.523 ↓ 6,581.8 39,491 1

GroupAggregate (cost=7,505.78..7,506.03 rows=6 width=59) (actual time=11,842.551..17,161.523 rows=39,491 loops=1)

  • Group Key: keyhistoryinfo.keyholder
2. 2,240.926 12,448.681 ↓ 222,069.2 1,332,415 1

Sort (cost=7,505.78..7,505.79 rows=6 width=78) (actual time=11,842.454..12,448.681 rows=1,332,415 loops=1)

  • Sort Key: keyhistoryinfo.keyholder
  • Sort Method: external merge Disk: 55216kB
3. 469.673 10,207.755 ↓ 222,069.2 1,332,415 1

Nested Loop (cost=6,191.15..7,505.70 rows=6 width=78) (actual time=1,388.888..10,207.755 rows=1,332,415 loops=1)

4. 324.136 1,466.434 ↓ 69.3 125,328 1

HashAggregate (cost=6,190.58..6,208.66 rows=1,808 width=258) (actual time=1,384.784..1,466.434 rows=125,328 loops=1)

  • Group Key: keyhistoryinfo.houseid, keyhistoryinfo.lockid, keyhistoryinfo.keyholder, keyhistoryinfo.userrole
5. 540.341 1,142.298 ↓ 110.2 199,199 1

Gather (cost=1,075.57..6,172.50 rows=1,808 width=258) (actual time=243.749..1,142.298 rows=199,199 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 12.682 601.957 ↓ 36.7 66,400 3 / 3

Parallel Append (cost=75.57..4,991.70 rows=1,808 width=258) (actual time=213.866..601.957 rows=66,400 loops=3)

7. 79.536 308.863 ↓ 98.2 48,327 3 / 3

Hash Join (cost=75.57..2,825.87 rows=492 width=66) (actual time=156.976..308.863 rows=48,327 loops=3)

  • Hash Cond: ((keyhistoryinfo.houseid)::text = (houseinfo.houseid)::text)
8. 72.475 72.475 ↑ 1.2 79,779 3 / 3

Parallel Seq Scan on keyhistoryinfo (cost=0.00..2,489.34 rows=99,410 width=66) (actual time=0.018..72.475 rows=79,779 loops=3)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 7359
9. 43.280 156.852 ↓ 89.5 53,243 3 / 3

Hash (cost=68.13..68.13 rows=595 width=35) (actual time=156.852..156.852 rows=53,243 loops=3)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3892kB
10. 113.572 113.572 ↓ 89.5 53,243 3 / 3

Index Scan using houseinfo_left_idx on houseinfo (cost=0.42..68.13 rows=595 width=35) (actual time=0.055..113.572 rows=53,243 loops=3)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
11. 49.951 280.412 ↓ 103.9 27,110 2 / 3

Hash Join (cost=75.57..2,138.71 rows=261 width=70) (actual time=211.150..420.618 rows=27,110 loops=2)

  • Hash Cond: ((lockkeyinfo.houseid)::text = (houseinfo_1.houseid)::text)
12. 89.808 89.808 ↓ 1.2 62,897 2 / 3

Parallel Seq Scan on lockkeyinfo (cost=0.00..1,925.81 rows=52,318 width=70) (actual time=0.019..134.712 rows=62,897 loops=2)

  • Filter: (userrole <> 255)
  • Rows Removed by Filter: 40486
13. 42.914 140.653 ↓ 89.5 53,243 2 / 3

Hash (cost=68.13..68.13 rows=595 width=35) (actual time=210.980..210.980 rows=53,243 loops=2)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3892kB
14. 97.739 97.739 ↓ 89.5 53,243 2 / 3

Index Scan using houseinfo_left_idx on houseinfo houseinfo_1 (cost=0.42..68.13 rows=595 width=35) (actual time=0.103..146.609 rows=53,243 loops=2)

  • Index Cond: ("left"((xzqhdm)::text, 6) = '330522'::text)
15. 8,271.648 8,271.648 ↓ 11.0 11 125,328

Index Only Scan using opendoorrecord_lockid_userid_opentime_idx on opendoorrecord (cost=0.57..0.70 rows=1 width=51) (actual time=0.061..0.066 rows=11 loops=125,328)

  • Index Cond: ((lockid = (keyhistoryinfo.lockid)::text) AND (userid = (keyhistoryinfo.keyholder)::text) AND (opentime > (((timezone('PRC'::text, now()) - '30 days'::interval))::date)::text))
  • Heap Fetches: 14937
Planning time : 22.466 ms
Execution time : 17,185.671 ms