I am using RDS Aurora PostgreSQL and I am new in PostgreSQL. I have been trying to analyze Execution Plans for queries in PostgreSQL.
I realized that manual audit on these plans take times, may need strict focus, and heavy effort as well. For example, this plan is complex for me.
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)"
I am looking for a tool to guide me about execution plans. For instance, if there is any full-table-scan or if query does not use any index, the tool can highlight it etc.
Do you know any tool/linter to help analyzing execution plans ?
CodePudding user response：
https://explain.depesz.com/ is the one I always use. It has suggestions, and things like SeqScan are clickable and lead you to the related article.
Also, consider running explain (verbose, analyze, buffers) for better explains.
CodePudding user response：
In Connecting to an Amazon Aurora PostgreSQL DB cluster, AWS recommends pgAdmin as a GUI tool.
pgAdmin lets you generate the Explain or Explain Analyze plan of a query by clicking the Explain or Explain Analyze button in the toolbar. This opens the Explain Panel, which shows the explain plan as a graph and as a table, as well as statistics for the plan and for the table.
You can also use Performance Insights from the AWS console to monitor database load, identify expensive queries and generate their execution plans. This is useful for discovering and diagnosing performance issues in production or load tests.