explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W8uq

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 441.401 ↓ 33.0 33 1

Limit (cost=3,429.25..3,429.53 rows=1 width=84) (actual time=437.040..441.401 rows=33 loops=1)

2. 4.678 441.389 ↓ 33.0 33 1

Result (cost=3,429.25..3,429.53 rows=1 width=84) (actual time=437.039..441.389 rows=33 loops=1)

3. 9.637 436.711 ↓ 33.0 33 1

Sort (cost=3,429.25..3,429.26 rows=1 width=52) (actual time=436.703..436.711 rows=33 loops=1)

  • Sort Key: (CASE amphur.province_code WHEN '9'::text THEN 1 WHEN '10'::text THEN 2 WHEN '12'::text THEN 3 ELSE 4 END), (((COALESCE(ou_rev.short_prefix, ''::character varying))::text || (COALESCE(ou_rev.name, ''::character varying))::text))
  • Sort Method: quicksort Memory: 29kB
4. 0.034 427.074 ↓ 33.0 33 1

Nested Loop Left Join (cost=782.22..3,429.24 rows=1 width=52) (actual time=331.803..427.074 rows=33 loops=1)

5. 0.032 426.908 ↓ 33.0 33 1

Nested Loop Left Join (cost=781.95..3,428.92 rows=1 width=96) (actual time=331.787..426.908 rows=33 loops=1)

6. 3.070 426.711 ↓ 33.0 33 1

Nested Loop (cost=781.66..3,420.61 rows=1 width=98) (actual time=331.773..426.711 rows=33 loops=1)

7. 13.892 337.100 ↓ 14.9 28,847 1

Hash Semi Join (cost=781.37..2,087.48 rows=1,931 width=32) (actual time=317.215..337.100 rows=28,847 loops=1)

  • Hash Cond: (ou.id = (CASE WHEN "group$ou".is_include_child_ou THEN gen.get_ou_id_child("group$ou".ou_id) ELSE "group$ou".ou_id END))
8. 6.012 6.012 ↑ 1.0 28,857 1

Seq Scan on ou (cost=0.00..1,208.87 rows=28,857 width=16) (actual time=0.005..6.012 rows=28,857 loops=1)

  • Filter: is_current
  • Rows Removed by Filter: 1030
9. 3.078 317.196 ↓ 14.9 29,876 1

Hash (cost=756.37..756.37 rows=2,000 width=16) (actual time=317.196..317.196 rows=29,876 loops=1)

  • Buckets: 32768 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1657kB
10. 6.774 314.118 ↓ 14.9 29,876 1

Unique (cost=726.37..736.37 rows=2,000 width=16) (actual time=299.216..314.118 rows=29,876 loops=1)

11. 62.808 307.344 ↓ 40,944.0 81,888 1

Sort (cost=726.37..726.38 rows=2 width=16) (actual time=299.214..307.344 rows=81,888 loops=1)

  • Sort Key: (CASE WHEN "group$ou".is_include_child_ou THEN gen.get_ou_id_child("group$ou".ou_id) ELSE "group$ou".ou_id END)
  • Sort Method: external merge Disk: 2080kB
12. 221.890 244.536 ↓ 40,944.0 81,888 1

Nested Loop Left Join (cost=54.90..726.36 rows=2 width=16) (actual time=15.645..244.536 rows=81,888 loops=1)

13. 0.022 21.492 ↑ 1.0 2 1

Nested Loop (cost=50.48..702.24 rows=2 width=62) (actual time=14.450..21.492 rows=2 loops=1)

14. 5.390 5.390 ↑ 1.0 12 1

Index Only Scan using groups_code_idx on groups (cost=0.41..53.13 rows=12 width=62) (actual time=0.630..5.390 rows=12 loops=1)

  • Index Cond: (code = ANY ('{central_adm_srvc_ar,central_usr_srvc_ar,central_งด_สบช,central_งด_3_น้ำทิพย์,central_งด_3_น้ำทิพย์_2,central_กลุ่มงานเงินเดือนและการถือจ่าย,central_งด_ผบ.หลัก_บค.,central_งด_3_น้ำทิพย์_3,"central_กลุ่มบริหารวงเงิน ผุสดี",central_administrator_MAIN,central_administrator_EDIT,central_งด_ทำคำสั่ง}'::text[]))
  • Heap Fetches: 0
15. 0.024 16.080 ↓ 0.0 0 12

Bitmap Heap Scan on "group$permission" (cost=50.07..54.08 rows=1 width=63) (actual time=1.340..1.340 rows=0 loops=12)

  • Recheck Cond: (((group_code)::text = (groups.code)::text) AND ((permission_code)::text = 'EMP-001'::text))
  • Heap Blocks: exact=2
16. 0.072 16.056 ↓ 0.0 0 12

BitmapAnd (cost=50.07..50.07 rows=1 width=0) (actual time=1.338..1.338 rows=0 loops=12)

17. 13.884 13.884 ↓ 2.3 352 12

Bitmap Index Scan on "group$permission_group_code_idx" (cost=0.00..9.70 rows=153 width=0) (actual time=1.157..1.157 rows=352 loops=12)

  • Index Cond: ((group_code)::text = (groups.code)::text)
18. 2.100 2.100 ↓ 1.1 1,611 12

Bitmap Index Scan on "group$permission_permission_code_idx" (cost=0.00..39.74 rows=1,509 width=0) (actual time=0.175..0.175 rows=1,611 loops=12)

  • Index Cond: ((permission_code)::text = 'EMP-001'::text)
19. 0.028 1.154 ↓ 10.5 21 2

Bitmap Heap Scan on "group$ou" (cost=4.43..11.79 rows=2 width=75) (actual time=0.566..0.577 rows=21 loops=2)

  • Recheck Cond: ((group_code)::text = (groups.code)::text)
  • Heap Blocks: exact=5
20. 1.126 1.126 ↓ 10.5 21 2

Bitmap Index Scan on "group$ou_group_code_idx" (cost=0.00..4.42 rows=2 width=0) (actual time=0.563..0.563 rows=21 loops=2)

  • Index Cond: ((group_code)::text = (groups.code)::text)
21. 86.541 86.541 ↓ 0.0 0 28,847

Index Scan using ou_rev_ou_id_idx on ou_rev (cost=0.29..0.68 rows=1 width=98) (actual time=0.003..0.003 rows=0 loops=28,847)

  • Index Cond: (ou_id = ou.id)
  • Filter: (is_current AND (((COALESCE(short_prefix, ''::character varying))::text || (name)::text) ~~ concat('%', 'รพศ', '%')))
  • Rows Removed by Filter: 1
22. 0.165 0.165 ↑ 1.0 1 33

Index Scan using "district_code_isAct_idx" on district (cost=0.29..8.30 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=33)

  • Index Cond: ((code)::text = (ou_rev.district_code)::text)
23. 0.132 0.132 ↑ 1.0 1 33

Index Scan using "amphur_code_isAct_idx" on amphur (cost=0.28..0.31 rows=1 width=9) (actual time=0.004..0.004 rows=1 loops=33)

  • Index Cond: ((code)::text = (district.amphur_code)::text)
Planning time : 53.746 ms
Execution time : 444.441 ms