Re: Bundle of patches - Mailing list pgsql-patches

> 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: teodor@sigaev.ru
                                                    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);