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

=?UTF-8?B?5Zyf5Y2c55q/?= <pengcz(dot)nwpu(at)gmail(dot)com> writes:
> NOTE: Version is 8.4 Fedora 20 X86_64

You do realize that's five years out of date? Not sure why you're running
an end-of-life database on a bleeding-edge OS.

> for understanding optimizer's internals, I
> *set debug_print_plan=on*
> ...
> 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!

The printed plan tree is actually the only one that the planner follows to
completion; other alternatives are discarded as soon as possible to save
useless processing.

For a query as simple as you're showing here, there aren't all that many
alternatives. You can probably explore all of them by fooling with the
planner parameters enable_seqscan etc, by repeatedly disallowing whatever
plan the planner thought was cheapest so that it will select and print
the next cheapest.

If you want to look more closely than that, you could add some code to the
add_path() subroutine so that it prints rejected paths --- but they'll
necessarily be pretty abstract and not user-friendly (or perhaps I should
say even less user-friendly than EXPLAIN usually is :-() because the
details simply aren't there.

There's lots of previous discussion in the PG archives, eg here here
and here:
http://www.postgresql.org/message-id/flat/CAN3Hncy1X9Zm4gJjGPc4ApYQe0Qs_pjZe=vw0V_J=rMa-cLF1g(at)mail(dot)gmail(dot)com
http://www.postgresql.org/message-id/flat/CANp-BfaRAAH2f9a55WqSanH4TrBeErFP_G3KaRwC-jLU-KX38A(at)mail(dot)gmail(dot)com
http://www.postgresql.org/message-id/flat/CAFcOn2-9j4fTcJ39xvdCByF6dg3U_=TGumCCp1-7SH_J9G+GtA(at)mail(dot)gmail(dot)com

I'm fairly sure that I remember seeing some more-completely-worked-out
patches for printing partial paths as they're considered, but my search fu
is failing me at the moment.

regards, tom lane