explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S1Ge : Optimization for: plan #ve3W

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 63.124 708.427 ↓ 85,128.5 170,257 1

Merge Join (cost=8,852.26..9,796.09 rows=2 width=87) (actual time=501.628..708.427 rows=170,257 loops=1)

  • Merge Cond: (p.pid = pnc.pid)
2. 91.241 611.273 ↓ 95,877.0 95,877 1

GroupAggregate (cost=8,851.84..8,851.87 rows=1 width=81) (actual time=501.603..611.273 rows=95,877 loops=1)

  • Group Key: p.pid, ai.classid
3. 289.925 520.032 ↓ 97,378.0 97,378 1

Sort (cost=8,851.84..8,851.85 rows=1 width=24) (actual time=501.577..520.032 rows=97,378 loops=1)

  • Sort Key: p.pid, ai.classid
  • Sort Method: quicksort Memory: 10389kB
4. 18.919 230.107 ↓ 99,275.0 99,275 1

Gather (cost=7,252.69..8,851.83 rows=1 width=24) (actual time=192.130..230.107 rows=99,275 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 23.771 211.188 ↓ 33,092.0 33,092 3

Parallel Hash Join (cost=6,252.69..7,851.73 rows=1 width=24) (actual time=184.964..211.188 rows=33,092 loops=3)

  • Hash Cond: ((cd.classid)::text = (ai.classid)::text)
6. 6.541 6.541 ↑ 1.2 27,568 3

Parallel Index Only Scan using c_1__psclassdefnu_classid_idx on c_1__psclassdefn cd (cost=0.42..1,470.23 rows=34,460 width=15) (actual time=0.160..6.541 rows=27,568 loops=3)

  • Heap Fetches: 0
7. 33.701 180.876 ↓ 33,092.0 33,092 3

Parallel Hash (cost=6,252.26..6,252.26 rows=1 width=24) (actual time=180.875..180.876 rows=33,092 loops=3)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7928kB
8. 44.686 147.175 ↓ 33,092.0 33,092 3

Hash Join (cost=1,964.65..6,252.26 rows=1 width=24) (actual time=96.680..147.175 rows=33,092 loops=3)

  • Hash Cond: (((ai.menuname)::text = (mi.menuname)::text) AND ((ai.barname)::text = (mi.barname)::text) AND ((ai.baritemname)::text = (mi.itemname)::text) AND ((ai.pnlitemname)::text = (pg.itemname)::text))
9. 5.855 5.855 ↑ 1.8 33,447 3

Parallel Seq Scan on c_1__psauthitem ai (cost=0.00..3,402.24 rows=59,024 width=76) (actual time=0.016..5.855 rows=33,447 loops=3)

10. 12.759 96.634 ↓ 12,886.0 12,886 3

Hash (cost=1,964.63..1,964.63 rows=1 width=117) (actual time=96.634..96.634 rows=12,886 loops=3)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2087kB
11. 14.879 83.875 ↓ 12,886.0 12,886 3

Hash Join (cost=1,393.24..1,964.63 rows=1 width=117) (actual time=67.200..83.875 rows=12,886 loops=3)

  • Hash Cond: (((pg.itemname)::text = (p.pnlitemname)::text) AND ((pg.pnlgrpname)::text = (mi.pnlgrpname)::text) AND ((pg.pnlname)::text = (p.pnlname)::text))
12. 2.453 2.453 ↑ 1.0 11,406 3

Seq Scan on c_1__pspnlgroup pg (cost=0.00..443.06 rows=11,406 width=45) (actual time=0.635..2.453 rows=11,406 loops=3)

13. 13.443 66.543 ↓ 12,667.0 12,667 3

Hash (cost=1,393.23..1,393.23 rows=1 width=145) (actual time=66.542..66.543 rows=12,667 loops=3)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2417kB
14. 36.155 53.100 ↓ 12,667.0 12,667 3

Merge Join (cost=2.21..1,393.23 rows=1 width=145) (actual time=0.084..53.100 rows=12,667 loops=3)

  • 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)
15. 9.723 9.723 ↑ 1.0 12,819 3

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.024..9.723 rows=12,819 loops=3)

16. 7.222 7.222 ↓ 1.6 13,622 3

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.032..7.222 rows=13,622 loops=3)

17. 34.030 34.030 ↓ 8.3 170,251 1

Index Only Scan using c_1__snt_nav_componentpid_pnc_idx on c_1__snt_nav_component pnc (cost=0.41..892.81 rows=20,553 width=14) (actual time=0.018..34.030 rows=170,251 loops=1)

  • Heap Fetches: 0
Planning time : 36.426 ms
Execution time : 719.604 ms