We have migrated to new Google sites!
When application user complaints that SQL query performance is degrading over time, here are the useful steps to debug the issue in detail.
1. Check the stats/last analyzed of the tables: This is to ensure that Query plan is using up-to-date stats. If Stats are stale, run analyze on table with stale statistics.
select objname,actionname,statime from pg_stat_operations a
where actionname='ANALYZE'
and upper(objname) in ('list of table name in the SQL query')
and statime=(select max(statime) from pg_stat_operations b where a.objname=b.objname and actionname='ANALYZE')
order by objname
2. Check gp_bloat_diag view for bloat
select * from gp_toolkit.gp_bloat_diag where bdinspname='schema_name' and upper(bdirelname) in ('list of table name in the SQL query')
3. Check gp_stats_missing view for missing stats
select * from gp_toolkit.gp_stats_missing where smischema='schema_name' and upper(smitable) in ('list of table name in the SQL query');
4. Check distribution keys of the tables
select table_owner,table_name,coalesce(distribution_keys, 'RANDOMLY')
from
(
SELECT pgn.nspname as table_owner,pgc.relname as table_name,pga.attname as distribution_keys
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER BY pgn.nspname,
pgc.relname
) a
where table_owner='schema_name'
and upper(table_name) in ('list of table name in the SQL query');
5. Check distributions/Skew of the table
SELECT COUNT(*), gp_segment_id FROM <table_name> GROUP BY gp_segment_id;
SELECT 'table_name' as "Table Name",max(c) as "Max Seg Rows",
min(c) as "Min Seg Rows",
(max(c)-min(c))*100.0/max(c) as "Percentage Difference Between Max & Min"
from (SELECT count(*) c, gp_segment_id FROM <table_name> group by 2) as a;
select skcoid,skcnamespace,skcrelname,skccoeff
from gp_toolkit.gp_skew_coefficients
where skcnamespace='schema_name'
and upper(skcrelname) in <table_name>;
you can also check gp_skew_idle_fractions view.
6. Run Explain/explain analyze plan on the SQL.