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