pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedReally really slow query. What's a better way?
3+ messages / 2 participants
[nested] [flat]
* Really really slow query. What's a better way?
@ 2006-02-24 06:54 Brendan Duddridge <brendan@clickspace.com>
0 siblings, 1 reply; 3+ messages in thread
From: Brendan Duddridge @ 2006-02-24 06:54 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tony Copping <tony@clickspace.com>
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
Attachments:
smime.p7s (application/pkcs7-signature, 2.3K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Really really slow query. What's a better way?
@ 2006-02-24 07:06 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Brendan Duddridge <brendan@clickspace.com>
0 siblings, 1 reply; 3+ messages in thread
From: Christopher Kings-Lynne @ 2006-02-24 07:06 UTC (permalink / raw)
To: Brendan Duddridge <brendan@clickspace.com>; +Cc: pgsql-performance; Tony Copping <tony@clickspace.com>
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Really really slow query. What's a better way?
@ 2006-02-24 07:24 Brendan Duddridge <brendan@clickspace.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 3+ messages in thread
From: Brendan Duddridge @ 2006-02-24 07:24 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-performance; Tony Copping <tony@clickspace.com>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-02-24 07:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-24 06:54 Really really slow query. What's a better way? Brendan Duddridge <brendan@clickspace.com>
2006-02-24 07:06 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-02-24 07:24 ` Brendan Duddridge <brendan@clickspace.com>
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