This is not responding to my concern. What you presented was an
Sorry, I see your point now.
Timing is with the same test suite, the same notebook, the same compiling
options and patch (attached) which measures total time of
keybased_rewrite_index_paths() call.
Time in table is measured in microseconds (not milliseconds!):
Number of clauses: | 2 | 3 | 4
----------------------------------------------------
not improve | 123-333 | 209-566 | 359-876
improve | 138-486 | 962 | 1588
Details:
a) Patch isn't able to improve
# select * from foo where (f1>40000 and f1<50000) or (f1>45000 and f1<70000)
order by f1, f2 limit 10;
NOTICE: Elapsed time 0.000333 sec
# explain select * from foo where (f1>40000 and f1<50000) or (f1>45000 and
f1<70000) or (f1>65000 and f1<80000) order by f1, f2 limit 10;
NOTICE: Elapsed time 0.000566 sec
# explain select * from foo where (f1>40000 and f1<50000) or (f1>45000 and
f1<70000) or (f1>65000 and f1<80000) or ( f1>75000 and f1<90000 ) order by f1,
f2 limit 10;
NOTICE: Elapsed time 0.000876 sec
# explain select * from foo where (f1>40000 and f1<50000) or (f2>45 and f2<46)
order by f1, f2 limit 10;
NOTICE: Elapsed time 0.000123 sec
# explain select * from foo where (f1>40000 and f1<50000) or (f2>45 and f2<46)
or (f1<30000 and f2<36) order by f1, f2 limit 10;
NOTICE: Elapsed time 0.000209 sec
# explain select * from foo where (f1>40000 and f1<50000) or (f2>45 and f2<46)
or (f1<30000 and f2<36) or (f1>45000 and f2>5) order by f1, f2 limit 10;
NOTICE: Elapsed time 0.000359 sec
b) Successful improving
# select * from foo where (f1=70000 and f2>95) or f1>70000 order by f1, f2
limit 10;
NOTICE: Elapsed time 0.000414 sec
# select * from foo where (f1>40000 and f1<50000) or (f1>60000 and f1<70000)
order by f1, f2 limit 10;
NOTICE: Elapsed time 0.000486 sec
# select * from foo where (f1>49980 and f1<50000) or (f1>69980 and f1<70000)
order by f1, f2 limit 10;
NOTICE: Elapsed time 0.000478 sec
# select * from foo where (f1=70000 and f2>95) or f1>40000 order by f1, f2
limit 10;
NOTICE: Elapsed time 0.000138 sec
2) tree clause
# select * from foo where (f1>40000 and f1<50000) or (f1>60000 and f1<70000) or
(f1>80000 and f1<90000)order by f1, f2 limit 10;
NOTICE: Elapsed time 0.000962 sec
3) 4 clauses
# select * from foo where (f1>40000 and f1<50000) or (f1>60000 and f1<70000) or
(f1>80000 and f1<90000) or (f1>95000 and f1<96000) order by f1, f2 limit 10;
NOTICE: Elapsed time 0.001588 sec
Unsuccessful
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
*** ./src/backend/optimizer/path/allpaths.c.orig Wed Dec 6 20:48:56 2006
--- ./src/backend/optimizer/path/allpaths.c Wed Dec 6 21:41:05 2006
***************
*** 32,37 ****
--- 32,38 ----
#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
+ #include <sys/time.h>
/* These parameters are set by GUC */
bool enable_geqo = false; /* just in case GUC doesn't set it */
***************
*** 189,194 ****
--- 190,200 ----
#endif
}
+ static double
+ timediff(struct timeval *begin, struct timeval *end) {
+ return ((double)( end->tv_sec - begin->tv_sec )) + ( (double)(
end->tv_usec-begin->tv_usec ) ) / 1.0e+6;
+ }
+
/*
* set_plain_rel_pathlist
* Build access paths for a plain relation (no subquery, no inheritance)
***************
*** 196,201 ****
--- 202,209 ----
static void
set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
{
+ struct timeval begin, end;
+
/* Mark rel with estimated output rows, width, etc */
set_baserel_size_estimates(root, rel);
***************
*** 243,249 ****
--- 251,260 ----
create_index_paths(root, rel);
/* Consider index scans with rewrited quals */
+ gettimeofday(&begin,NULL);
keybased_rewrite_index_paths(root, rel);
+ gettimeofday(&end,NULL);
+ elog(NOTICE,"Elapsed time %g sec", timediff(&begin, &end));
/* Consider TID scans */
create_tidscan_paths(root, rel);
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
