Re: Really really slow query. What's a better way?

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 |  brendan@clickspace.com 
> 
> *
> *ClickSpace Interactive Inc.
> Suite L100, 239 - 10th Ave. SE
> Calgary, AB  T2G 0V9
> 
> http://www.clickspace.com 
>