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