Skip site navigation (1) Skip section navigation (2)

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

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: Brendan Duddridge <brendan(at)clickspace(dot)com>
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>,Tony Copping <tony(at)clickspace(dot)com>
Subject: Re: Really really slow query. What's a better way?
Date: 2006-02-24 07:06:34
Message-ID: 43FEB07A.7080209@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
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 
> 


In response to

Responses

pgsql-performance by date

Next:From: Brendan DuddridgeDate: 2006-02-24 07:24:00
Subject: Re: Really really slow query. What's a better way?
Previous:From: Brendan DuddridgeDate: 2006-02-24 06:54:45
Subject: Really really slow query. What's a better way?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group