Re: How can I make PostgreSQL to select another quey plan?

Yangyang <yangyangbm(at)gmail(dot)com> writes:
> My proposal is like this:
> Suppose for the same query, Plan A consumes 10 units of I/O and 50
> units of CPU. Plan B consumes 20 units of I/O and 40 units of CPU.
> If A has less total cost than B, A will be selected as best plan.
> If the database has less CPU available, I would prefer it to select
> Plan B, which consumes less units of CPU.

No doubt, but the question I was asking is whether you have a clear idea
of what Plan B is and whether the Postgres query planner can even
generate that plan from your query.

> The query I used is :
> select
> l_returnflag,
> l_linestatus,
> sum(l_quantity) as sum_qty,
> sum(l_extendedprice) as sum_base_price,
> sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
> sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
> avg(l_quantity) as avg_qty,
> avg(l_extendedprice) as avg_price,
> avg(l_discount) as avg_disc,
> count(*) as count_order
> from
> lineitem
> where
> l_shipdate <= date('1998-12-01') - interval '90 days'
> group by
> l_returnflag,
> l_linestatus
> order by
> l_returnflag,
> l_linestatus;

Hmm ... after looking up the TPC-H spec, that seems to correspond to Q1,
whereas earlier you said you were using Q4. Since your plan shows a
sub-select, and Q1 contains no sub-select but Q4 does, I'm going to
suppose you meant Q4.

Now the next question is why you're getting a subplan at all. I'd
expect Postgres 8.4 and up to recognize Q4's WHERE EXISTS(SELECT ...)
as a semi-join, rather than implementing it as an unoptimized sub-select
which is what your plan is showing. If I'm right in deducing that you
are testing an ancient version of Postgres, then the first step to
improvement is to get onto a modern version. The unoptimized sub-select
framework does not provide very much room at all for alternative plans
--- it's basically always going to amount to a nestloop join. Which is
probably why you're not getting anywhere by fooling with the planner
cost settings.

regards, tom lane