Friday, February 08. 2008

One of our favorite features of PgAdmin is the graphical explain plan feature. While a graphical explain plan is not a complete
substitute for EXPLAIN or EXPLAIN ANALYZE text plans, it does provide a quick and easy to read view that can be used for further analysis.
In this article, we'll walk thru using
the explain plan to troubleshoot query performance.

To use the graphical explain plan feature in PgAdmin III - do the following

Launch PgAdmin III and select a database.

Click the SQL icon

Type in a query or set of queries, and highlight the text of the query you want to analyse.

Click the F7 button or go under Query->Explain or click the Explain Query icon .

If you see no graphical explain plan, make sure that Query->Explain options->Verbose is unchecked - otherwise graphical explain will not work

In terms of Explain option under the Query->Explain options-> you can choose Analyze which will give you the actual Explain plan in use and actual time and will take longer to run. Unchecking
this feature gives you the approximate explain plan and does not include time since its approximate. In terms of the graphical display - the raw display doesn't look too different between the 2, but if you click
on a section of the graph, a little tip will pop up showing the stats for that part of the graph. For analyze, you will see time metrics in the tip.

Here we look at the graphical explain plan of the two below queries querying against the demo pagila database.
Note that with constraint_exclusion off, both these
two queries have similar plans as shown in diagram below.

Now if we turn constraint_exclusion on - which, by the way you can make a global setting, in your PostgreSQL installation by modifying postgresql.conf
or via PgAdmin->Tools->Server Configuration->postgresql.conf. If you make a global setting, make sure to restart your PostgreSQL service/daemon or reload the config.

and similar constraints on the other inherited payment tables to exclude them from queries where they should never have results.

As said earlier, to look into the details of timings etc, we can click on a section of the graphical explain plan
as shown here or just look at the raw explain plan text.

From the above we observe that with constrain exclusion on, our query has fewer tables to inspect. Constraint Exclusion is turned off by default in PostgreSQL config, so if you use partitioned tables, make sure to enable it in your postgresql.conf file. We also learn that
our query is doing a sequential scan of the data instead of an index scan. What if we added an index on the payment_date field, like so

One may ask, that if the planner is capable of using 2 indexes simultaneously with bitmap heap index scans, why did it not
do so in this case without forcing its hand? If we compare the timings between the two approaches, they turn out to be pretty much the
same. Was the planner wrong not to use the customer index? Probably not. The reason is that even though we
have an index in place for customer_id on both tables, the fact that the customer list and number of customers purchasing items is
so small, doing a sequential table scan may be more efficient than using the index. Keep in mind that the planner
thinks like an economist, it sees the index as a resource and a resource that takes energy to consume. Consuming that resource
may be more costly than ignoring it. This is important to keep in mind for cases where you have small tables that don't expect
to increase or fields such as boolean fields. It is often wasteful to put indexes on these since they will rarely be used since a table
scan is more effective and indexes have cost in terms of needing to be updated during insert/update and having the planner even have to consider them. The planner uses table statistics to determine if an index is worthwhile to use. It is important that after large inserts/updates of data, one does a
VACUUM ANALYZE to update statistics.

In this case, over time, the number of customers will increase so while the planner determines it is not useful to use now, increase in
data may change its mind.

There is another interesting thing about the graphical explain plan which is hard to see in these fairly simple examples.
There is meaning to the thickness of lines in the plan. A thicker line means more costly than a thinner line.

The graphical explain diagram also has a plethora of cute icons to display the various strategies in use which makes
it easy to spot problematic areas - especially for fairly large plans. Below is a query that
pulls the last sale for period February 2007 to March 15th 2007 for each customer who ordered during that period and sorts the
customers by last name and first name.

Constraint Exclusion when it fails to work
What is Constraint Exclusion?
Constraint Exclusion is a feature introduced in PostgreSQL 8.1 which is used in conjunction with Table Inheritance to implement
table partitioning strategies. The basic idea is you put check constraints on tables to limit

Weblog: Postgres OnLine JournalTracked: Mar 28, 15:57

Why is my index not being used?
The age old question of why or why is my table index not being used
is probably the most common question that ever gets asked even by expert database users.
In this brief article we will cover the most common reasons and try to order by statistical si

That is one reason. It is also often because the planner doesn't have perfect information. For example if you just loaded a lot of data and analyze hasn't been run yet, the cost that the planner is putting on index may be very wrong.

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.Enter the string from the spam-prevention image above: