Re: I feel the need for speed. What am I doing wrong? - Mailing list pgsql-hackers

From Nigel J. Andrews
Subject Re: I feel the need for speed. What am I doing wrong?
Date
Msg-id Pine.LNX.4.21.0301070046410.22599-100000@ponder.fairway2k.co.uk
Whole thread Raw
In response to I feel the need for speed. What am I doing wrong?  ("Dann Corbit" <DCorbit@connx.com>)
Responses Re: I feel the need for speed. What am I doing wrong?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Neil Conway
Date:
Subject: Re: New Portal in Place, DNS switched ...
Next
From: Justin Clift
Date:
Subject: OS/400 support?