explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ve3W

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 106.831 5,101.771 ↓ 85,128.5 170,257 1

Nested Loop (cost=1,395.43..1,396.62 rows=2 width=87) (actual time=4,672.188..5,101.771 rows=170,257 loops=1)

2. 101.350 4,799.390 ↓ 97,775.0 97,775 1

GroupAggregate (cost=1,395.01..1,395.04 rows=1 width=81) (actual time=4,672.071..4,799.390 rows=97,775 loops=1)

  • Group Key: p.pid, ai.classid
3. 340.654 4,698.040 ↓ 99,275.0 99,275 1

Sort (cost=1,395.01..1,395.02 rows=1 width=24) (actual time=4,672.054..4,698.040 rows=99,275 loops=1)

  • Sort Key: p.pid, ai.classid
  • Sort Method: external merge Disk: 3496kB
4. 251.224 4,357.386 ↓ 99,275.0 99,275 1

Nested Loop (cost=3.46..1,395.00 rows=1 width=24) (actual time=0.244..4,357.386 rows=99,275 loops=1)

  • Join Filter: (((mi.pnlgrpname)::text = (pg.pnlgrpname)::text) AND ((p.pnlname)::text = (pg.pnlname)::text))
  • Rows Removed by Join Filter: 637621
5. 95.431 2,346.068 ↓ 97,783.0 97,783 1

Nested Loop (cost=3.05..1,394.56 rows=1 width=97) (actual time=0.190..2,346.068 rows=97,783 loops=1)

6. 86.764 588.326 ↓ 97,783.0 97,783 1

Nested Loop (cost=2.63..1,394.10 rows=1 width=97) (actual time=0.085..588.326 rows=97,783 loops=1)

  • Join Filter: (((mi.menuname)::text = (ai.menuname)::text) AND ((mi.barname)::text = (ai.barname)::text) AND ((mi.itemname)::text = (ai.baritemname)::text))
7. 47.595 70.884 ↓ 12,667.0 12,667 1

Merge Join (cost=2.21..1,393.23 rows=1 width=145) (actual time=0.042..70.884 rows=12,667 loops=1)

  • Merge Cond: (((p.menuname)::text = (mi.menuname)::text) AND ((p.barname)::text = (mi.barname)::text) AND ((p.baritemname)::text = (mi.itemname)::text))
  • Join Filter: ((mi.pnlgrpname)::text = (p.pnlgrpname)::text)
8. 12.364 12.364 ↑ 1.0 12,819 1

Index Scan using c1_hr_reg_menu_pagesmbbppp_idx on c1_hr_reg_menu_pages p (cost=0.41..621.68 rows=12,819 width=91) (actual time=0.009..12.364 rows=12,819 loops=1)

9. 10.925 10.925 ↓ 1.6 13,622 1

Index Scan using c_1__psmenuitemmbb_idx on c_1__psmenuitem mi (cost=0.41..609.51 rows=8,727 width=54) (actual time=0.011..10.925 rows=13,622 loops=1)

10. 430.678 430.678 ↓ 8.0 8 12,667

Index Scan using c_1__psauthitemmbbp_idx on c_1__psauthitem ai (cost=0.42..0.86 rows=1 width=76) (actual time=0.023..0.034 rows=8 loops=12,667)

  • Index Cond: (((menuname)::text = (p.menuname)::text) AND ((barname)::text = (p.barname)::text) AND ((baritemname)::text = (p.baritemname)::text) AND ((pnlitemname)::text = (p.pnlitemname)::text))
11. 1,662.311 1,662.311 ↑ 1.0 1 97,783

Index Only Scan using c_1__psclassdefnu_classid_idx on c_1__psclassdefn cd (cost=0.42..0.44 rows=1 width=15) (actual time=0.017..0.017 rows=1 loops=97,783)

  • Index Cond: (classid = (ai.classid)::text)
  • Heap Fetches: 0
12. 1,760.094 1,760.094 ↓ 8.0 8 97,783

Index Scan using c_1__pspnlgroupmbb_idx on c_1__pspnlgroup pg (cost=0.41..0.43 rows=1 width=45) (actual time=0.014..0.018 rows=8 loops=97,783)

  • Index Cond: ((itemname)::text = (ai.pnlitemname)::text)
13. 195.550 195.550 ↑ 1.0 2 97,775

Index Only Scan using c_1__snt_nav_componentpid_pnc_idx on c_1__snt_nav_component pnc (cost=0.41..1.55 rows=2 width=14) (actual time=0.002..0.002 rows=2 loops=97,775)

  • Index Cond: (pid = p.pid)
  • Heap Fetches: 0
Planning time : 43.153 ms
Execution time : 5,115.148 ms