Re: whether I can see other alternative plantrees for one query?

Hi,

> hi, all
>   NOTE: Version is 8.4   Fedora 20 X86_64

Why don't you play on 9.3 or later? 8.4 is now on the edge to EOL.

>   for understanding optimizer's internals, I set debug_print_plan=on
> and created two tables as follows :
> 
> create table Reserves (sid integer, bid integer,day date,rname char(25));
> create table Sailors(sid integer,sname char(25),rating integer,age real);
> 
> and add 1,000,000 records for each.
> 
> and execute the cmd:
> 
> select S.rating,count(*)
>         from Sailors S
>         where S.rating > 5 and S.age = 20
>         group by S.rating;
> 
> but from the log, I only found the final selected planTree, so I want to
> ask:
> what should I do if I want to see the other alternative planTrees?  any
> advice will be apprecitaed!

Forcing another plan by configuration parameters would help.

http://www.postgresql.org/docs/9.3/static/runtime-config-query.html

For example, "set enable_hashagg to off" makes the planner to try
to avoid using HashAggregate for grouping. If you got a plan
using HashAgregate, you will get another one using GroupAggregate
by that.

What you can do otherwise would be building PG with
CFLAGS="-DOPTIMIZER_DEBUG". This will show you a bit more than
debug_print_plan, but the query you mentioned is too simple so
that planner has almost no alternative. Creating some index (say,
on age) would give planner some alternatives.

Have a good day,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center