> -----Original Message-----
> From: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk] 
> Sent: Monday, January 06, 2003 4:58 PM
> To: Dann Corbit
> Cc: pgsql-hackers@postgresql.org; pgsql-general@postgresql.org
> Subject: Re: [HACKERS] I feel the need for speed. What am I 
> doing wrong?
> 
> Added -general list so that the next followup can remove 
> -hackers and everyone there will have had notice.
> 
> 
> On Mon, 6 Jan 2003, Dann Corbit wrote:
> >
> > I have a query using two postgres tables.
> > One is called "CNX_DS_53_SIS_STU_OPT_FEE_TB" and the other is called
> > "CNX_DS2_53_SIS_STU_OPT_FEE_TB".
> >  
> > I am getting 3 times slower performance than Microsoft Access when 
> > performing a left outer join.
> >
> > ...
> > 
> > Here is the query:
> > select a."RT_REC_KEY", a."cnxarraycolumn", a."CRC" from
> > "CNX_DS_53_SIS_STU_OPT_FEE_TB" a left outer join
> > "CNX_DS2_53_SIS_STU_OPT_FEE_TB" b on ( a."RT_REC_KEY" = 
> b."RT_REC_KEY"
> > and a."cnxarraycolumn" = b."cnxarraycolumn") where b.oid is null ;
> >  
> >  
> > Creating the following index had no effect on performance! create 
> > unique index i1 on "CNX_DS2_53_SIS_STU_OPT_FEE_TB" ("RT_REC_KEY", 
> > "cnxarraycolumn", "CRC");
> >  
> > Both tables had 6139062 rows of data.
> >  
> > In this query ... all rows of data match perfectly, so no 
> results are 
> > returned.
> 
> I suspect you get no results because it's unlikely b.oid will 
> be null. Are you sure the query is how it should be since you 
> seem to be expecting no rows to be returned and yet your 
> reason for that doesn't match the query as shown. Without the 
> oid test I'd bet you get a result set of 6139062 rows.

There are supposed to be no results for this particular query.
The data is unique with only RT_REC_KEY and cnxarraycolumn (I tried
using that as an index also).

The basic gist of it is like this:

I want to know where the keys match (e.g.: RT_REC_KEY + cnxarraycolumn)
but the CRC has changed (which will tell me what data has changed).
This is for a data synchronization system that uses PostgreSQL as a join
engine.  I store primary key data together with a 64 bit CRC in data
tables stored in PostgreSQL.  In this particular case, there were no
changes but there will be changes at other times.
 
> > Is there a way to reformulate this query so that it will use the 
> > index?
> 
> Given the above comment I'd say no since the entirety of both 
> tables will be tested to make the result set.
> 
> Alternatively, if the query is right try something along the lines of:
> 
> SELECT a.blah, a.foo,
>   FROM a, b
>   WHERE a.blah = b.blah AND a.foo = b.foo AND b.oid IS NULL

OID is never null.   I don't think that this query is equivalent.  This
Oid is just the standard PostgreSQL Oid, and not some arbitrary field.
 
> if that doesn't use a query try pushing the null test into a 
> subselect like:
> 
> SELECT a.blah, a.foo,
>   FROM a, (SELECT * FROM b WHERE oid IS NULL) b
>   WHERE a.blah = b.blah AND a.foo = b.foo
> 

OID is never null.   I don't think that this query is equivalent.
 
> After that let's hope I haven't embarrassed myself.

Probably, I did not explain myself clearly enough.

Mon, 6 Jan 2003 17:10:41 -0800
"Dann Corbit" <DCorbit@connx.com>
Re: I feel the need for speed.  What am I doing wrong?