我正在使用RDS Aurora PostgreSQL,我是PostgreSQL的新手.我一直在try 分析PostgreSQL中查询的执行计划.
我意识到,对这些计划的手动审计需要时间,可能需要严格的重点,而且还需要付出巨大的努力.例如,这个计划对我来说很复杂.
QUERY PLAN
Hash Full Join (cost=952458.62..1003967.53 rows=84905 width=114)
" Hash Cond: (((s.id)::text = (dp.siteid)::text) AND ((s.organizationid)::text = (dp.organizationid)::text))"
" -> Hash Full Join (cost=764029.81..811418.96 rows=84905 width=106)"
" Hash Cond: (((s.id)::text = (fl.siteid)::text) AND ((s.organizationid)::text = (fl.organizationid)::text))"
" -> Hash Full Join (cost=551181.40..594312.81 rows=84905 width=98)"
" Hash Cond: (((s.id)::text = (l.siteid)::text) AND ((s.organizationid)::text = (l.organizationid)::text))"
" -> Hash Full Join (cost=381576.41..424262.07 rows=84905 width=90)"
" Hash Cond: (((s.id)::text = (n.siteid)::text) AND ((s.organizationid)::text = (n.organizationid)::text))"
" -> Merge Full Join (cost=187046.05..226005.95 rows=84905 width=82)"
" Merge Cond: (((s.organizationid)::text = (device.organizationid)::text) AND ((s.id)::text = (device.siteid)::text))"
" -> Index Only Scan using idx_site_org_site_heartbeat on site s (cost=0.41..13536.51 rows=29107 width=74)"
" -> Finalize GroupAggregate (cost=187045.64..211050.29 rows=84905 width=82)"
" Group Key: device.organizationid, device.siteid"
" -> Gather Merge (cost=187045.64..208927.67 rows=169810 width=82)"
" Workers Planned: 2"
" -> Partial GroupAggregate (cost=186045.62..188327.36 rows=84905 width=82)"
" Group Key: device.organizationid, device.siteid"
" -> Sort (cost=186045.62..186403.79 rows=143269 width=74)"
" Sort Key: device.organizationid, device.siteid"
" -> Parallel Seq Scan on device (cost=0.00..167409.30 rows=143269 width=74)"
" Filter: ispartofsite"
" -> Hash (cost=192521.93..192521.93 rows=70029 width=82)"
" -> Subquery Scan on n (cost=176213.83..192521.93 rows=70029 width=82)"
" -> Finalize GroupAggregate (cost=176213.83..191821.64 rows=70029 width=82)"
" Group Key: device_1.organizationid, device_1.siteid"
" -> Gather Merge (cost=176213.83..190280.22 rows=112150 width=82)"
" Workers Planned: 2"
" -> Partial GroupAggregate (cost=175213.81..176335.31 rows=56075 width=82)"
" Group Key: device_1.organizationid, device_1.siteid"
" -> Sort (cost=175213.81..175353.99 rows=56075 width=74)"
" Sort Key: device_1.organizationid, device_1.siteid"
" -> Parallel Seq Scan on device device_1 (cost=0.00..168298.87 rows=56075 width=74)"
" Filter: (ispartofsite AND (networkstatus = 1))"
" -> Hash (cost=169577.50..169577.50 rows=1833 width=82)"
" -> Subquery Scan on l (cost=169335.87..169577.50 rows=1833 width=82)"
" -> Finalize GroupAggregate (cost=169335.87..169559.17 rows=1833 width=82)"
" Group Key: device_2.organizationid, device_2.siteid"
" -> Gather Merge (cost=169335.87..169529.27 rows=1542 width=82)"
" Workers Planned: 2"
" -> Partial GroupAggregate (cost=168335.84..168351.26 rows=771 width=82)"
" Group Key: device_2.organizationid, device_2.siteid"
" -> Sort (cost=168335.84..168337.77 rows=771 width=74)"
" Sort Key: device_2.organizationid, device_2.siteid"
" -> Parallel Seq Scan on device device_2 (cost=0.00..168298.87 rows=771 width=74)"
" Filter: (ispartofsite AND (dailynetworkloss >= 3))"
" -> Hash (cost=210420.92..210420.92 rows=84632 width=82)"
" -> Subquery Scan on fl (cost=185738.00..210420.92 rows=84632 width=82)"
" -> Finalize GroupAggregate (cost=185738.00..209574.60 rows=84632 width=82)"
" Group Key: device_3.organizationid, device_3.siteid"
" -> Gather Merge (cost=185738.00..207458.80 rows=169264 width=82)"
" Workers Planned: 2"
" -> Partial GroupAggregate (cost=184737.97..186921.51 rows=84632 width=82)"
" Group Key: device_3.organizationid, device_3.siteid"
" -> Sort (cost=184737.97..185072.28 rows=133722 width=74)"
" Sort Key: device_3.organizationid, device_3.siteid"
" -> Parallel Seq Scan on device device_3 (cost=0.00..167409.30 rows=133722 width=74)"
" Filter: (ispartofsite AND (NOT firmwareinformation_islatest))"
" -> Hash (cost=186637.96..186637.96 rows=62457 width=82)"
" -> Subquery Scan on dp (cost=173737.69..186637.96 rows=62457 width=82)"
" -> Finalize GroupAggregate (cost=173737.69..186013.39 rows=62457 width=82)"
" Group Key: device_4.organizationid, device_4.siteid"
" -> Gather Merge (cost=173737.69..184731.43 rows=87652 width=82)"
" Workers Planned: 2"
" -> Partial GroupAggregate (cost=172737.67..173614.19 rows=43826 width=82)"
" Group Key: device_4.organizationid, device_4.siteid"
" -> Sort (cost=172737.67..172847.24 rows=43826 width=74)"
我正在寻找一种工具来指导我的执行计划.例如,如果有任何全表扫描,或者如果查询不使用任何索引,该工具可以突出显示它,等等.
你知道什么工具可以帮助你分析执行计划吗?