Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Brendan Duddridge
Date:
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 

Attachment

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

From
Christopher Kings-Lynne
Date:
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
>

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

From
Brendan Duddridge
Date:
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
>
Attachment