using indexes with the OR clause

In the "Connect Settings" (either global or per data source) add the
following line:
SET ksqo TO 'on'

Stands for "Key Set Query Oprimizer". It actually breaked those nasty
OR's into UNIONS. There will eventually be a radio button for this.
We have be in a crunch lately at work.

Are you using row versioning? If so, you may have to overload an
operator for > on xid.

Let me know how it goes.

Sferacarta Software wrote:

Jose' Soares wrote:

David Hartwig wrote:

In the "Connect Settings" (either global or per data source) add the
following line:
SET ksqo TO 'on'

Stands for "Key Set Query Oprimizer". It actually breaked those nasty
OR's into UNIONS. There will eventually be a radio button for this.
We have be in a crunch lately at work.

Yes, this works well now.

Are you using row versioning? If so, you may have to overload an
operator for > on xid.

Do you mean < I think, because my log says ERROR: Unable to find an
ordering operator '<' for type xid
Any way. I tried to create the operators =, < and > but I have some
troubles to do this.

I modified xidint4.c and xidint4.sql, the sources that you sent me some
time ago, as follow:

---------------xidint4.c-------------
/* Insight Distribution Systems - System V - Apr 1998i
static char accntnum_c[] = "@(#)accntnum.c 1.1
/sccs/sql/extend/s.accntnum.
*/
#include <stdio.h> /* for sprintf() */
#include <string.h>
#include "postgres.h"
#include "utils/palloc.h"

bool xidint4_eq(int32 arg1, int32 arg2);
bool xidint4_gt(int32 arg1, int32 arg2);
bool xidint4_lt(int32 arg1, int32 arg2);

bool xidint4_eq(int32 arg1, int32 arg2)
{
return (arg1 == arg2);
}

bool xidint4_gt(int32 arg1, int32 arg2)
{
return (arg1 > arg2);
}

bool xidint4_lt(int32 arg1, int32 arg2)
{
return (arg1 < arg2);
}

--------------------xidint4.sql---------------------------
create function xidint4_eq(xid,int4)
returns bool
as '/usr/local/pgsql/lib/contrib/xidint4.so'
language 'c';

create function xidint4_gt(xid,int4)
returns bool
as '/usr/local/pgsql/lib/contrib/xidint4.so'
language 'c';

create function xidint4_lt(xid,int4)
returns bool
as '/usr/local/pgsql/lib/contrib/xidint4.so'
language 'c';

create operator = (
leftarg=xid,
rightarg=int4,
procedure=xidint4_eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);

create operator < (
leftarg=xid,
rightarg=int4,
procedure=xidint4_lt,
commutator='<',
negator='>',
restrict=intltsel,
join=intltjoinsel
);

create operator > (
leftarg=xid,
rightarg=int4,
procedure=xidint4_gt,
commutator='>',
negator='<',
restrict=intgtsel,
join=intgtjoinsel
);

This script gives me this message for every operator it creates,
NOTICE: buffer leak [392] detected in BufferPoolCheckLeak()
CREATE

but at end seems that it works, I can query a table as:

select xmin from attivita where xmin = 92017;
select xmin from attivita where xmin > 92016;
select xmin from attivita where xmin < 92018;

But psqlodbc.log has still the message:

ERROR: Unable to find an ordering operator '<' for type xid

Thanks David for your help.

Jose'

Hmmm... Sound a bit strange. Lets keep an eye on it. Anyway, I have a
minimalist patch which is confined to SQL which accomplishes the same thing.
I just overload the int4 functions. Works well. If you use this patch, be
sure to DROP the other XID operators and functions that you just created to
remove any ambiguity.

++++++++++++++++++++++++++++++++++++++

create function int4eq(xid,int4)
returns bool
as ''
language 'internal';

create operator = (
leftarg=xid,
rightarg=int4,
procedure=int4eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);

create function int4lt(xid,xid)
returns bool
as ''
language 'internal';

create function int4lt(xid,xid)
returns bool
as ''
language 'internal';

create operator < (
leftarg=xid,
rightarg=xid,
procedure=int4lt,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);