Horton
Menu

Thread

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

    Brendan Duddridge <brendan@clickspace.com> — 2006-02-24T06:54:45Z

    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

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

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2006-02-24T07:06:34Z

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

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

    Brendan Duddridge <brendan@clickspace.com> — 2006-02-24T07:24:00Z

    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@clickspace.com

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@clickspace.com <mailto:brendan@clickspace.com>
>> *
>> *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
>