Query Profiling in Greenplum database

posted Sep 14, 2012, 4:32 PM by Sachchida Ojha
Greenplum Database devises a query plan for each query it is given. Choosing the right query plan to match the query and data structure is absolutely critical for good performance. A query plan defines how the query will be executed in Greenplum Database’s parallel execution environment. By examining the query plans of poorly performing queries, you can identify possible performance tuning opportunities.

The query planner uses the database statistics it has to choose a query plan with the lowest possible cost. Cost is measured in disk I/O and CPU effort (shown as units of disk page fetches). The goal is to minimize the total execution cost for the plan.

You can view the plan for a given query using the EXPLAIN command. This will show the query planner’s estimated plan for the query. 

For example:

EXPLAIN SELECT * FROM names WHERE id=32;

EXPLAIN ANALYZE causes the statement to be actually executed, not only planned. This is useful for seeing whether the planner’s estimates are close to reality. 

For example:

EXPLAIN ANALYZE SELECT * FROM names WHERE id=32;

Comments