Hello. The performance drag was not so large after all. For all that, I agree that the opition that this kind of separate multiple-nested loops on relations, joins or ECs and so on for searching something should be avoided. I personally feel that additional time to such an extent (around 1%) would be tolerable if it affected a wide range of queries or it brought more obvious gain.
Unfortunately I can't decide this to be 'ready for commiter' for now. I think we should have this on smaller footprint, in a method without separate exhauxtive searching. I also had very similar problem in the past but I haven't find such a way for my problem.. At Wed, 11 Mar 2015 01:32:24 +1300, David Rowley <dgrowle...@gmail.com> wrote in <CAApHDvpEXAjs6mV2ro4=3qbzpx=plrteinx0j2yhq6wrp85...@mail.gmail.com> > > explain select t1.a, t10.b from t1 join t2 on (t1.b = t2.a) join t3 on > > (t2.b = t3.a) join t4 on (t3.b = t4.a) join t5 on (t4.b = t5.a) join t6 on > > (t5.b = t6.a) join t7 on (t6.b = t7.a) join t8 on (t7.b = t8.a) join t9 on > > (t8.b = t9.a) join t10 on (t9.b = t10.a); > > > > The head takes 3ms for planning and the patched version takes > > around 5ms while pure execution time is 1ms.. I think it is a too > > long extra time. > > > > > This smells quite fishy to me. I'd be quite surprised if your machine took > an extra 2 ms to do this. You're right. Sorry. I was amazed by the numbers.. I took again the times for both master and patched on master (some conflict arised). Configured with no options so compiled with -O2 and no assertions. Measured the planning time for the test query 10 times and calcualted the average. patched: 1.883ms (stddev 0.034) master: 1.861ms (stddev 0.042) About 0.02ms, 1% extra time looks to be taken by the extra processing. regards, > I've run what I think is the same test on my 5 year old i5 laptop and > attached the .sql file which I used to generate the same schema as you've > described. > > I've also attached the results of the explain analyze "Planning Time:" > output from patched and unpatched using your test case. > > I was unable to notice any difference in plan times between both versions. > In fact master came out slower, which is likely just the noise in the > results. > > Just to see how long mark_unique_joins() takes with your test case I > changed query_planner() to call mark_unique_joins() 1 million times: > > { > int x; > for (x = 0; x < 1000000;x++) > mark_unique_joins(root, joinlist); > } > > > I also got rid of the fast path test which bails out if the join is already > marked as unique. > > /* check if we've already marked this join as unique on a previous call */ > /*if (idxrel->is_unique_join) > return true; > */ > > On my machine after making these changes, it takes 800 ms to plan the > query. So it seems that's around 800 nano seconds for a single call to > mark_unique_joins(). > > Perhaps you've accidentally compiled the patched version with debug asserts? > > Are you able to retest this? -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers