Search code examples
sqldatabase-administrationvertica

Vertica query performance tuning

Lets say you have a slow query, what is the chronological order of the steps you will take to solve the problem ?

  • Check the cluster ?

vnetperf / vioperf / vcpuperf

  • Overview the query plan ?

EXPLAIN / EXPLAIN LOCAL VERBOSE / PROFILE

  • Query any system tables ?

EXECUTION_ENGINE_PROFILES / QUERY_EVENTS / DC_QUERY_EXECUTIONS / EXECUTION_ENGINE_PROFILES / DC_SIPS_STATISTICS / QUERY_PLAN_PROFILES / RESOURCE_ALLOCATION / PROJECTION_USAGE / PROJECTIONS_COLUMN / PROJECTIONS_STORAGE

  • Projection design ?
Solution

  • as a 1 step look at the query and query plan see if you have stale statistics or unnecessary network resegments, check if your projections match your queries. also check recorded session from last year BDC it kinda helpfull : vertica query tuning