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 are the tables in question:

connxdatasync=# \d "CNX_DS_53_SIS_STU_OPT_FEE_TB"
   Table "CNX_DS_53_SIS_STU_OPT_FEE_TB"
   Attribute    |     Type      | Modifier
----------------+---------------+----------
 RT_REC_KEY     | character(14) |
 cnxarraycolumn | integer       |
 CRC            | bigint        | not null
 
connxdatasync=# \d "CNX_DS2_53_SIS_STU_OPT_FEE_TB"
   Table "CNX_DS2_53_SIS_STU_OPT_FEE_TB"
   Attribute    |     Type      | Modifier
----------------+---------------+----------
 RT_REC_KEY     | character(14) |
 cnxarraycolumn | integer       |
 CRC            | bigint        | not null

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.

Is there a way to reformulate this query so that it will use the index?

Mon, 6 Jan 2003 16:21:30 -0800
"Dann Corbit" <DCorbit@connx.com>
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.

> 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

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


After that let's hope I haven't embarrassed myself.


-- 
Nigel J. Andrews


Tue, 7 Jan 2003 00:57:45 +0000 (GMT)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: [HACKERS] I feel the need for speed.  What am I doing wrong?
"Dann Corbit" <DCorbit@connx.com> writes:
> 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");

What does EXPLAIN ANALYZE have to say about the query?  If you set
enable_seqscan = 0, does the explain result change?

			regards, tom lane

Mon, 06 Jan 2003 20:21:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: I feel the need for speed. What am I doing wrong? 
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
>> 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 ;

> I suspect you get no results because it's unlikely b.oid will be null.

Try "it's impossible for b.oid to be null --- unless a dummy b row is
being provided by the LEFT JOIN".  I interpret the purpose of the query
to be to look for "a" rows that have no matching "b" row.

Using OID for this is kind of cute, I guess, since it is guaranteed
not-null in a real row; he doesn't have to think about whether any of
his regular columns are not-null.

			regards, tom lane

Mon, 06 Jan 2003 20:25:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] I feel the need for speed. What am I doing wrong? 