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

Re: [HACKERS] OR clause issues

From: Vadim Mikheev <vadim(at)krs(dot)ru>
To: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
Cc: PostgreSQL-development <hackers(at)postgreSQL(dot)org>
Subject: Re: [HACKERS] OR clause issues
Date: 1998-07-27 16:04:56
Message-ID: 35BCA528.382F4593@krs.ru (view raw or flat)
Thread:
Lists: pgsql-hackers
Bruce Momjian wrote:
> 
> We have two problems.  First, indexes are not used with OR's.  This is a
> serious problem, with no good workaround.   I have looked at the code,
> and there are two places that need changes.  First, there is much code
> in the optimizer to handle OR's, but it was turned off because it did
> not work.  There is also no support in the executor to handle multiple
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^
> OR values when using indexes.  I have fixed the optimizer so it can now
> identify OR clauses and handle them properly:
> 
>         test=> explain select * from test where x=3 or x=4;
>         NOTICE:  equal: don't know whether nodes of type 200 are equal
>         NOTICE:  QUERY PLAN:
> 
>         Index Scan using i_test on test  (cost=4.10 size=1 width=4)
> 
> As you can see, I am getting a NOTICE I have to check into.  Also, the
> executor is only returning the FIRST of the OR conditions, because I
> have not yet added code to nodeIndexscan.c to handle multiple values.
> 
> This code is not installed in the main source tree.  I will complete my
> cleanups and tests, and install it.  I may need help with
> nodeIndexscan.c.  My idea is to hook up multiple ScanKeys, and to move
> on to the next one when the first finishes.  Perhaps someone (Vadim?)
> could help as I am a little lost in how to do that.  Pointers to similar
> code would help.

execnodes.h:

/* ----------------
 *   IndexScanState information
 *
 *      IndexPtr           current index in use
 *      NumIndices         number of indices in this scan
 *      ScanKeys           Skey structures to scan index rels
 *      NumScanKeys        array of no of keys in each Skey struct

- some support is already in Executor!
Functions in nodeIndexscan.c also handle this.

Currently, IndexPtr is ALWAYS ZERO - so you have to add code to 
switch to the next index after NULL is returned by index_getnext()
(in IndexNext()).

Note that different indices (of the same table) may be used 
in single scan (x = 3 or y = 1)!

The most complex stuff to be implemented for something
like (x = 3 or y = 1) is to check that for tuples, fetched
by second index sub-scan, x IS NOT EQUAL 3!
Maybe IndexScan->indxqual can help you...

Vadim

In response to

pgsql-hackers by date

Next:From: Vadim MikheevDate: 1998-07-27 20:31:38
Subject: Re: [HACKERS] Q about read committed in Oracle...
Previous:From: J Christopher BowerDate: 1998-07-27 15:31:36
Subject: 6.1 pg_dump core dump

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