Re: [GENERAL] I feel the need for speed. What am I doing wrong?

> -----Original Message-----
> From: Jean-Luc Lachance [mailto:jllachan@nsd.ca]
> Sent: Tuesday, January 07, 2003 2:43 PM
> To: Tom Lane
> Cc: Dann Corbit; Nigel J. Andrews;
> pgsql-hackers@postgresql.org; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] [HACKERS] I feel the need for speed.
> What am I doing wrong?
>
>
> There is a construct that most people forget for that kind of query:
>
> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from a
> except
> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from b;
>
> simple.

I should have mentioned that I am not using the latest version of
PostgreSQL.  I am using 7.1.3.  Perhaps this stuff has been repaired in
newer versions.  Possibly, there is a reason that people forget to use
it (at least on PostgreSQL 7.1.3):

connxdatasync=> SET enable_seqscan = 0;
SET VARIABLE
connxdatasync=>
connxdatasync=> SELECT a."RT_REC_KEY", a."cnxarraycolumn", a."CRC" FROM
connxdatasync-> "CNX_DS_53_SIS_STU_OPT_FEE_TB" a
connxdatasync-> LEFT OUTER JOIN
connxdatasync-> "CNX_DS2_53_SIS_STU_OPT_FEE_TB" b
connxdatasync->         ON ( a."RT_REC_KEY" = b."RT_REC_KEY" AND
a."cnxarraycolumn" = b."cnxarraycolumn")
connxdatasync-> WHERE b.oid IS NULL ;
 RT_REC_KEY | cnxarraycolumn | CRC
------------+----------------+-----
(0 rows)

1:55.12 to complete

connxdatasync=>
connxdatasync=> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
"CNX_DS_53_SIS_STU_OPT_FEE_TB" a
connxdatasync-> except
connxdatasync-> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
"CNX_DS2_53_SIS_STU_OPT_FEE_TB" b;
 RT_REC_KEY | cnxarraycolumn | CRC
------------+----------------+-----
(0 rows)

12:55.25 to complete: More than 6 times slower to complete.

connxdatasync=>
connxdatasync=> EXPLAIN
connxdatasync-> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
"CNX_DS_53_SIS_STU_OPT_FEE_TB" a
connxdatasync-> except
connxdatasync-> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
"CNX_DS2_53_SIS_STU_OPT_FEE_TB" b;
NOTICE:  QUERY PLAN:

SetOp Except  (cost=202028537.97..202120623.90 rows=1227812 width=24)
  ->  Sort  (cost=202028537.97..202028537.97 rows=12278124 width=24)
        ->  Append  (cost=100000000.00..200225099.24 rows=12278124
width=24)
              ->  Subquery Scan *SELECT* 1
(cost=100000000.00..100112549.62 rows=6139062 width=24)
                    ->  Seq Scan on CNX_DS_53_SIS_STU_OPT_FEE_TB a
(cost=100000000.00..100112549.62 rows=6139062 width=24)
              ->  Subquery Scan *SELECT* 2
(cost=100000000.00..100112549.62 rows=6139062 width=24)
                    ->  Seq Scan on CNX_DS2_53_SIS_STU_OPT_FEE_TB b
(cost=100000000.00..100112549.62 rows=6139062 width=24)

EXPLAIN
connxdatasync=>