How to check a query execution plans in PostgreSQL

[vijaylohkare]

HI All,

I want to know the command that will display all the execution plans which are generated by the parser in PostgreSQL.
Is there any command to instruct PostgreSQL to choose a specific query plan.

Regards
Vijay

[michaelb]

Welcome to the Forum!

You can use explain analyze, take a look at these pages:

http://www.postgresql.org/docs/8.0/interactive/sql-explain.html

http://www.postgresql.org/docs/8.0/interactive/performance-tips.html

http://www.postgresql.org/docs/8.0/interactive/indexes-examine.html

[vijaylohkare]

Welcome to the Forum!

You can use explain analyze, take a look at these pages:

http://www.postgresql.org/docs/8.0/interactive/sql-explain.html

http://www.postgresql.org/docs/8.0/interactive/performance-tips.html

http://www.postgresql.org/docs/8.0/i...s-examine.html

Hi

Thanks for it
I have already saw those links
My question is different.
The PostgreSQL generates multiple query plans for query
I want to see all of them and forcefully want to run one of those

[michaelb]

>> The PostgreSQL generates multiple query plans for query

What do you mean by that?
Different results for different executions of 'explain analyze', or something else?

As far as forcing a certain plan I am not aware of any way of doing that
(unless they introduced something new in the last Pg versions)

But you can certainly affect the execution plan indirectly by modifying some run time parameters

[vijaylohkare]

What do you mean by that?
Different results for different executions of 'explain analyze', or something else?

As far as forcing a certain plan I am not aware of any way of doing that
(unless they introduced something new in the last Pg versions)

But you can certainly affect the execution plan indirectly by modifying some run time parameters

HI
Thanks for reply my problem is different.
I want to know how to generate different query plans for a single query and to execute each of them forcefully so that i can test the correctness of parser whether it is giving the same result or not for same query but using multiple plans.Performan ce might affect but i am not worry about it for the time being

Thanks
Vijay

[michaelb]

The only way to generate multiple plans is again to tweak the run-time parameters I mentioned above.
In addition to that you may need to tweak your data.in some cases.
This is all I can think of.

I wonder though why do you need to go through this exercise.
You may not agree with this parallel, but I'll throw it in anyway.
If you bought a scientific calculator you use it. You don't think of running a bunch of tests to make sure that 2 * 2 * 2 will yield the same result as 2 to the 3rd degree. You just trust it because this is something basic, and you don't have any concerns unless and until you have a proved malfunction, or at least a serious suspicion that something isn't working correctly.

Same here, what you want to test seems so fundamental that I can't help wondering what made you think you need to test it.