Re: How can I show more potential query plans, other than the default one?

Yangyang <yangyangbm(at)gmail(dot)com> writes:
> I want to see more query plans, other than the default one for a query
> and compare their cost. Based on this, I can change the cost model and
> see how that can affect the cost model?
> I enabled the debug information in postgresql.conf. But it looks like
> the output is still for the best plan only. Is there any way to see
> them, before I go to the code and implement sth myself?

Only the best path is converted to plan form, and even the path space
is not searched completely in most cases, so the answer is that the
information you are wishing it would print simply doesn't exist.

I have seen patches to print paths as they're created (try looking in
the mailing list archives, particularly pgsql-hackers). I don't think
that's tremendously useful to most people, but it's the closest
approximation to what you asked for.

Less painful ways to investigate alternative plans include
* manipulating the enable_xxx planner control variables
* manipulating the cost variables (though of course then you should
not consider the estimated costs comparable)
* temporarily dropping an index that the original plan uses, ie
begin;
drop index ...;
explain ...;
rollback;

regards, tom lane