Re: KSQO parameter - Mailing list pgsql-hackers

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Is there any value to KQSO parameter?  It was for complex OR clauses.  I
> > thought Tom fixed most of that.
>
> The last set of tests that I did (~7.0) showed that it was still
> marginally faster than the default approach.  Not a "must have"
> like it used to be for those queries, though.
>
> As of 7.1 it is disabled anyway, because I didn't have time to update
> the KSQO code for the new implementation of UNION and friends.
>
> We should either update the code or remove it entirely.  I still don't
> have the time or interest to do #1, but I'm not quite ready to do #2
> either ... does anyone out there want to work on it?

The following patch removes KSQO from GUC and the call to the function.
It also moves the main KSQO file into _deadcode.  Applied.

ODBC folks, should I remove KSQO from the ODBC driver?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.118
diff -c -r1.118 runtime.sgml
*** doc/src/sgml/runtime.sgml    15 Jun 2002 19:58:53 -0000    1.118
--- doc/src/sgml/runtime.sgml    15 Jun 2002 23:27:27 -0000
***************
*** 760,793 ****
       </varlistentry>

       <varlistentry>
-       <term><varname>KSQO</varname> (<type>boolean</type>)</term>
-       <listitem>
-        <para>
-         The <firstterm>Key Set Query Optimizer</firstterm>
-         (<acronym>KSQO</acronym>) causes the query planner to convert
-         queries whose <literal>WHERE</> clause contains many OR'ed AND
-         clauses (such as <literal>WHERE (a=1 AND b=2) OR (a=2 AND b=3)
-         ...</literal>) into a union query. This method can be faster
-         than the default implementation, but it doesn't necessarily give
-         exactly the same results, since <literal>UNION</> implicitly
-         adds a <literal>SELECT DISTINCT</> clause to eliminate identical
-         output rows. <acronym>KSQO</acronym> is commonly used when
-         working with products like <productname>Microsoft
-         Access</productname>, which tend to generate queries of this
-         form.
-        </para>
-
-        <para>
-         The <acronym>KSQO</acronym> algorithm used to be absolutely
-         essential for queries with many OR'ed AND clauses, but in
-         <productname>PostgreSQL</productname> 7.0 and later the standard
-         planner handles these queries fairly successfully; hence the
-         default is off.
-        </para>
-       </listitem>
-      </varlistentry>
-
-      <varlistentry>
        <term><varname>RANDOM_PAGE_COST</varname> (<type>floating point</type>)</term>
        <listitem>
         <para>
--- 760,765 ----
Index: src/backend/optimizer/plan/planner.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v
retrieving revision 1.120
diff -c -r1.120 planner.c
*** src/backend/optimizer/plan/planner.c    13 Jun 2002 15:10:25 -0000    1.120
--- src/backend/optimizer/plan/planner.c    15 Jun 2002 23:27:29 -0000
***************
*** 145,155 ****
      PlannerQueryLevel++;
      PlannerInitPlan = NIL;

- #ifdef ENABLE_KEY_SET_QUERY
-     /* this should go away sometime soon */
-     transformKeySetQuery(parse);
- #endif
-
      /*
       * Check to see if any subqueries in the rangetable can be merged into
       * this query.
--- 145,150 ----
Index: src/backend/optimizer/prep/Makefile
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/prep/Makefile,v
retrieving revision 1.12
diff -c -r1.12 Makefile
*** src/backend/optimizer/prep/Makefile    31 Aug 2000 16:10:13 -0000    1.12
--- src/backend/optimizer/prep/Makefile    15 Jun 2002 23:27:29 -0000
***************
*** 12,18 ****
  top_builddir = ../../../..
  include $(top_builddir)/src/Makefile.global

! OBJS = prepqual.o preptlist.o prepunion.o prepkeyset.o

  all: SUBSYS.o

--- 12,18 ----
  top_builddir = ../../../..
  include $(top_builddir)/src/Makefile.global

! OBJS = prepqual.o preptlist.o prepunion.o

  all: SUBSYS.o

Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.69
diff -c -r1.69 guc.c
*** src/backend/utils/misc/guc.c    17 May 2002 20:32:29 -0000    1.69
--- src/backend/utils/misc/guc.c    15 Jun 2002 23:27:32 -0000
***************
*** 312,322 ****
          { "enable_hashjoin", PGC_USERSET }, &enable_hashjoin,
          true, NULL, NULL
      },
-
-     {
-         { "ksqo", PGC_USERSET }, &_use_keyset_query_optimizer,
-         false, NULL, NULL
-     },
      {
          { "geqo", PGC_USERSET }, &enable_geqo,
          true, NULL, NULL
--- 312,317 ----
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.39
diff -c -r1.39 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample    15 Jun 2002 01:29:50 -0000    1.39
--- src/backend/utils/misc/postgresql.conf.sample    15 Jun 2002 23:27:32 -0000
***************
*** 89,96 ****
  #enable_mergejoin = true
  #enable_hashjoin = true

- #ksqo = false
-
  #effective_cache_size = 1000    # default in 8k pages
  #random_page_cost = 4
  #cpu_tuple_cost = 0.01
--- 89,94 ----
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/tab-complete.c,v
retrieving revision 1.49
diff -c -r1.49 tab-complete.c
*** src/bin/psql/tab-complete.c    15 Jun 2002 19:43:47 -0000    1.49
--- src/bin/psql/tab-complete.c    15 Jun 2002 23:27:34 -0000
***************
*** 226,232 ****
          "enable_nestloop",
          "enable_mergejoin",
          "enable_hashjoin",
-         "ksqo",
          "geqo",
          "fsync",
          "server_min_messages",
--- 226,231 ----
***************
*** 695,701 ****

              COMPLETE_WITH_LIST(my_list);
          }
!         else if (strcasecmp(prev2_wd, "GEQO") == 0 || strcasecmp(prev2_wd, "KSQO") == 0)
          {
              char       *my_list[] = {"ON", "OFF", "DEFAULT", NULL};

--- 694,700 ----

              COMPLETE_WITH_LIST(my_list);
          }
!         else if (strcasecmp(prev2_wd, "GEQO") == 0)
          {
              char       *my_list[] = {"ON", "OFF", "DEFAULT", NULL};

Index: src/include/optimizer/planmain.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/optimizer/planmain.h,v
retrieving revision 1.57
diff -c -r1.57 planmain.h
*** src/include/optimizer/planmain.h    18 May 2002 02:25:50 -0000    1.57
--- src/include/optimizer/planmain.h    15 Jun 2002 23:27:34 -0000
***************
*** 62,72 ****
                               Index acceptable_rel);
  extern void fix_opids(Node *node);

- /*
-  * prep/prepkeyset.c
-  */
- extern bool _use_keyset_query_optimizer;
-
- extern void transformKeySetQuery(Query *origNode);
-
  #endif   /* PLANMAIN_H */
--- 62,65 ----