Thanks Chris for the very quick response!Just after posting this message, we tried explain on the same format as you just posted:
explain DELETE FROM cds.cds_mspecxx WHERE not exists (SELECT 'X' FROM cds_stage.cds_Catalog stage where stage.countryCode = 'us' and stage.prodid = cds.cds_mspecxx.prodid) and countryCode = 'us';
QUERY PLAN------------------------------------------------------------------------ ---------------------- Bitmap Heap Scan on cds_mspecxx (cost=299654.85..59555205.23 rows=7377879 width=6)
Recheck Cond: ((countrycode)::text = 'us'::text) Filter: (NOT (subplan))-> Bitmap Index Scan on pk_mspecxx (cost=0.00..299654.85 rows=14755759 width=0)
Index Cond: ((countrycode)::text = 'us'::text) SubPlan-> Index Scan using pk_catalog on cds_catalog stage (cost=0.00..7.97 rows=2 width=0) Index Cond: (((prodid)::text = ($0)::text) AND ((countrycode)::text = 'us'::text))
(8 rows)Seems way better. I'm not sure it can get any faster though. Not sure if having the indexes as (countryCode, ProdId) or (ProdId, countryCode) would make any kind of difference though. Would it?
Thanks! ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@xxxxxxxxxxxxxx ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Feb 24, 2006, at 12:06 AM, Christopher Kings-Lynne wrote:
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@xxxxxxxxxxxxxx <mailto:brendan@xxxxxxxxxxxxxx>* *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
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
