Обсуждение: whether I can see other alternative plantrees for one query?
hi, all
NOTE: Version is 8.4 Fedora 20 X86_64
for understanding optimizer's internals, I
set debug_print_plan=on
create table Reserves (sid integer, bid integer,day date,rname char(25));
create table Sailors(sid integer,sname char(25),rating integer,age real);
select S.rating,count(*)
from Sailors S
where S.rating > 5 and S.age = 20
group by S.rating;
土卜皿 <pengcz.nwpu@gmail.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@mail.gmail.com http://www.postgresql.org/message-id/flat/CANp-BfaRAAH2f9a55WqSanH4TrBeErFP_G3KaRwC-jLU-KX38A@mail.gmail.com http://www.postgresql.org/message-id/flat/CAFcOn2-9j4fTcJ39xvdCByF6dg3U_=TGumCCp1-7SH_J9G+GtA@mail.gmail.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
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