Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] Re: [HACKERS] changes in 6.4

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: daveh(at)insightdist(dot)com (David Hartwig)
Cc: hannu(at)trust(dot)ee, pgsql-interfaces(at)postgreSQL(dot)org, hackers(at)postgreSQL(dot)org
Subject: Re: [INTERFACES] Re: [HACKERS] changes in 6.4
Date: 1998-08-22 03:53:39
Message-ID: 199808220353.XAA04528@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackerspgsql-interfaces
> 
> Hannu Krosing wrote:
> 
> > > The days where every release fixed server crashes, or added a feature
> > > that users were 'screaming for' may be a thing of the past.
> >
> > Is anyone working on fixing the exploding optimisations for many OR-s,
> > at least the canonic case used by access?
> >
> > My impression is that this has fallen somewhere between
> > insightdist and Vadim.
> 
> This is really big for the ODBCers. (And I suspect for JDBCers too.)  Many
> desktop libraries and end-user tools depend on this "record set" strategy to
> operate effectively.
> 
> I have put together a workable hack that runs just before cnfify().  The
> option is activated through the SET command.  Once activated, it identifies
> queries with this particular multi-OR pattern generated by these RECORD SET
> strategies.  Qualified query trees are rewritten as multiple UNIONs.   (One
> for each OR grouping).
> 
> The results are profound.    Queries that used to scan tables because of the
> ORs, now make use of any indexes.   Thus, the size of the table has virtually
> no effect on performance.  Furthermore, queries that used to crash the
> backend, now run in under a second.
> 
> Currently the down sides are:
>     1. If there is no usable index, performance is significantly worse.  The
> patch does not check to make sure that there is a usable index.  I could use
> some pointers on this.
> 
>     2. Small tables are actually a bit slower than without the patch.
> 
>     3.  Not very elegant.    I am looking for a more generalized solution.
> I have lots of ideas, but I would need to know the backend much better before
> attempting any of them.   My favorite idea is before cnfify(), to factor the
> OR terms and pull out the constants into a virtual (temporary) table spaces.
> Then rewrite the query as a join.   The optimizer will (should) treat the new
> query accordingly.  This assumes that an efficient factoring algorithm exists
> and that temporary tables can exist in the heap.
> 
> Illustration:
> SELECT ... FROM tab WHERE
> (var1 = const1 AND var2 = const2) OR
> (var1 = const3 AND var2 = const4) OR
> (var1 = const5 AND var2 = const6)
> 
> SELECT ... FROM tab, tmp WHERE
> (var1 = var_x AND var2 = var_y)
> 
> tmp
> var_x  | var_y
> --------------
> const1|const2
> const3|const4
> const5|const6

David, where are we on this?  I know we have OR's using indexes.  Do we
still need to look this as a fix, or are we OK.   I have not gotten far
enough in the optimizer to know how to fix the cnf'ify problem. 


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 1998-08-22 04:09:29
Subject: Re: [HACKERS] subselects & views
Previous:From: Bruce MomjianDate: 1998-08-22 03:19:03
Subject: Re: [HACKERS] Some items for the TODO list

pgsql-interfaces by date

Next:From: Rolf LuetteckeDate: 1998-08-23 14:40:39
Subject: Backslash-problem
Previous:From: Thomas G. LockhartDate: 1998-08-22 02:21:16
Subject: Re: [INTERFACES] iodbc interface on Unix

pgsql-general by date

Next:From: Bruce MomjianDate: 1998-08-22 12:29:45
Subject: Re: [GENERAL] More details on Database corruption
Previous:From: David Ben-YaacovDate: 1998-08-21 22:01:25
Subject: Re: [GENERAL] DELETE statement KILL backend <AGAIN>

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group