how about something like:
DELETE FROM cds.cds_mspecxx WHERE NOT EXISTS (SELECT 1 FROM
cds_stage.cds_Catalog stage where stage.countryCode = 'us' and
stage.ProdId=cds.cds_mspecxx.ProdId) and countryCode = 'us';
Run explain on it first to see how it will be planned. Both tables
should have an index over (countryCode, ProdId) I think.
Chris
Brendan Duddridge wrote:
Hi,
We're executing a query that has the following plan and we're wondering
given the size of the data set, what's a better way to write the query?
It's been running since 2pm 2 days ago.
explain DELETE FROM cds.cds_mspecxx WHERE ProdID not in (SELECT
stage.ProdID FROM cds_stage.cds_Catalog stage where stage.countryCode =
'us') and countryCode = 'us';
QUERY PLAN
---------------------------------------------------------------------------------------------------
Index Scan using pk_mspecxx on cds_mspecxx
(cost=53360.87..208989078645.48 rows=7377879 width=6)
Index Cond: ((countrycode)::text = 'us'::text)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=53360.87..77607.54 rows=1629167 width=12)
-> Seq Scan on cds_catalog stage (cost=0.00..43776.70 rows=1629167 width=12)
Filter: ((countrycode)::text = 'us'::text)
(7 rows)
Thanks,
*
*____________________________________________________________________
*Brendan Duddridge* | CTO | 403-277-5591 x24 | [EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>
*
*ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
