> -----Original Message-----
> From: Jean-Luc Lachance [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, January 07, 2003 2:43 PM
> To: Tom Lane
> Cc: Dann Corbit; Nigel J. Andrews;
> [EMAIL PROTECTED]; [EMAIL PROTECTED]
> 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=>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
