Sample Query to show Cardinality estimation error in PostgreSQL

Question

I am working on a project using PostgreSQL9.3. I was using the below query to show how selectivity estimation errors can lead to multi-fold increase in query execution time on TPC-H workload using PostgreSQL8.3.

select 
    n_name, 
    sum(l_extendedprice * (1 - l_discount)) as revenue 
from 
    customer, 
    orders, 
    lineitem, 
    supplier, 
    nation,
    region 
where 
    c_custkey = o_custkey 
    and l_orderkey = o_orderkey 
    and l_suppkey = s_suppkey   
    and c_nationkey = s_nationkey 
    and s_nationkey = n_nationkey 
    and n_regionkey = r_regionkey 
    and (r_name='ASIA' or r_name='AFRICA') 
    and o_orderdate >= date '1994-01-01' 
    and o_orderdate < date '1994-01-01' + interval '1 year' 
    and l_shipdate <= l_receiptdate 
    and l_commitdate <= l_shipdate + integer '90' 
    and l_extendedprice <= 20000 
    and c_name like '%r#00%' 
    and c_acctbal <=2400 
group by 
    n_name 
order by    
    revenue desc

The problem was that PostgreSQL8.3 was choosing a plan which involves lot of NestedLoop joins since selectivity estimation on lineitem and customer were wrong by big margin. I think this was majorly due to LIKE pattern matching. But the optimal plan should have been using Hash Joins.

Recently I upgraded to PostgreSQL9.3 for my Project and observed that the above query no longer gives a bad plan. I spent some amount of time trying to find a query with large cardinality estimation error on TPC-H 1GB data with no success till now. Does any PostgreSQL geeks know some off the shelf query on TPC-H benchmark or any query to show cardinality estimation error in PostgreSQL9.3

Answer 1

This is to answer the comment by @Twelfth as well as the question itself.

Three quotes from this chapter in the manual:
"Controlling the Planner with Explicit JOIN Clauses"

Explicit inner join syntax (INNER JOIN, CROSS JOIN, or unadorned JOIN) is semantically the same as listing the input relations in FROM, so it does not constrain the join order.

...

To force the planner to follow the join order laid out by explicit JOINs, set the join_collapse_limit run-time parameter to 1. (Other possible values are discussed below.)

...

Constraining the planner's search in this way is a useful technique both for reducing planning time and for directing the planner to a good query plan.

Bold emphasis mine. Conversely, you can abuse the same to direct the query planner to a bad query plan for your testing purposes. Read the whole manual page. It should be instrumental.

Also, you can force nested loops by disabling alternative methods one by one (best in your session only). Like:

SET enable_hashjoin = off;

Etc.
About checking and setting parameters:

• Query a parameter (postgresql.conf setting) like "max_connections"

Force actual estimation errors

One obvious way would be to disable autovacuum and add / remove rows from the table. Then the query planner is working with outdated statistics. Note that some other commands update statistics as well.

Statistics are stored in the catalog tables pg_class and pg_statistics.

SELECT * FROM pg_class WHERE oid = 'mytable'::regclass;
SELECT * FROM pg_statistic WHERE starelid = 'mytable'::regclass;

This leads me to another option. You could forge entries in these two tables. Superuser privileges required.
You don't strike me as a newcomer, but a warning for the general public: If you break something in the catalog tables, your database (cluster) might go belly-up. You have been warned.