pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brendan Duddridge <brendan@clickspace.com>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Cc: Tony Copping <tony@clickspace.com>
Subject: Re: Really really slow query. What's a better way?
Date: Fri, 24 Feb 2006 00:24:00 -0700
Message-ID: <1E4DF43B-6BF9-4F16-AD27-2326288F6B8E@clickspace.com> (raw)
In-Reply-To: <43FEB07A.7080209@familyhealth.com.au>
References: <1831440F-F848-471D-ACE9-F84CE9F4E359@clickspace.com>
	<43FEB07A.7080209@familyhealth.com.au>

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
>

Attachments:

  smime.p7s (application/pkcs7-signature, 2.3K) download

view thread (3+ messages)

Message-ID: <1E4DF43B-6BF9-4F16-AD27-2326288F6B8E@clickspace.com>
Permalink:  ../1E4DF43B-6BF9-4F16-AD27-2326288F6B8E@clickspace.com/
Also on:    postgresql.org/message-id/1E4DF43B-6BF9-4F16-AD27-2326288F6B8E@clickspace.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: brendan@clickspace.com, chriskl@familyhealth.com.au, tony@clickspace.com
  Subject: Re: Really really slow query. What's a better way?
  In-Reply-To: <1E4DF43B-6BF9-4F16-AD27-2326288F6B8E@clickspace.com>
  References: <1831440F-F848-471D-ACE9-F84CE9F4E359@clickspace.com> <43FEB07A.7080209@familyhealth.com.au> <1E4DF43B-6BF9-4F16-AD27-2326288F6B8E@clickspace.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox