Version 8.3.1
On May 20, 2008, at 1:51 PM, kevin kempter wrote:
Hi all;
I have 2 tables where I basically want to delete from the first
table (seg_id_tmp7) any rows where the entire row already exists in
the second table (sl_cd_segment_dim)
I have a query that looks like this (and it's slow):
delete from seg_id_tmp7
where
customer_srcid::text ||
show_srcid::text ||
show_name::text ||
season_srcid::text ||
season_name::text ||
episode_srcid::text ||
episode_name::text ||
segment_type_id::text ||
segment_type::text ||
segment_srcid::text ||
segment_name::text
in
( select
customer_srcid::text ||
show_srcid::text ||
show_name::text ||
season_srcid::text ||
season_name::text ||
episode_srcid::text ||
episode_name::text ||
segment_type_id::text ||
segment_type::text ||
segment_srcid::text ||
segment_name::text
from sl_cd_location_dim )
;
Here's the query plan for it:
QUERY PLAN
-----------------------------------------------------------------------------------
Seq Scan on seg_id_tmp7 (cost=0.00..138870701.56 rows=2136 width=6)
Filter: (subplan)
SubPlan
-> Seq Scan on sl_cd_location_dim (cost=0.00..63931.60
rows=433040 width=8)
(4 rows)
I also tried this:
delete from seg_id_tmp7
where
( customer_srcid ,
show_srcid ,
show_name ,
season_srcid ,
season_name ,
episode_srcid ,
episode_name ,
segment_type_id ,
segment_type ,
segment_srcid ,
segment_name )
in
( select
customer_srcid ,
show_srcid ,
show_name ,
season_srcid ,
season_name ,
episode_srcid ,
episode_name ,
segment_type_id ,
segment_type ,
segment_srcid ,
segment_name
from sl_cd_location_dim )
;
and I get this query plan:
QUERY PLAN
-----------------------------------------------------------------------------------
Seq Scan on seg_id_tmp7 (cost=0.00..87997034.20 rows=2136 width=6)
Filter: (subplan)
SubPlan
-> Seq Scan on sl_cd_location_dim (cost=0.00..40114.40
rows=433040 width=8)
(4 rows)
If it helps here's the describe's (including indexes) for both tables:
# \d seg_id_tmp7
Table "public.seg_id_tmp7"
Column | Type | Modifiers
-----------------+-----------------------------+-----------
customer_srcid | bigint |
show_srcid | bigint |
show_name | character varying |
season_srcid | bigint |
season_name | character varying |
episode_srcid | bigint |
episode_name | character varying |
segment_type_id | bigint |
segment_type | character varying |
segment_srcid | bigint |
segment_name | character varying |
create_dt | timestamp without time zone |
# \d sl_cd_segment_dim
Table
"public.sl_cd_segment_dim"
Column | Type
| Modifiers
----------------------+-----------------------------
+-------------------------------------------------------------
sl_cd_segment_dim_id | bigint | not null
default nextval('sl_cd_segment_dim_seq'::regclass)
customer_srcid | bigint | not null
show_srcid | bigint | not null
show_name | character varying(500) | not null
season_srcid | bigint | not null
season_name | character varying(500) | not null
episode_srcid | bigint | not null
episode_name | character varying(500) | not null
segment_type_id | integer |
segment_type | character varying(500) |
segment_srcid | bigint |
segment_name | character varying(500) |
effective_dt | timestamp without time zone | not null
default now()
inactive_dt | timestamp without time zone |
last_update_dt | timestamp without time zone | not null
default now()
Indexes:
"sl_cd_segment_dim_pk" PRIMARY KEY, btree (sl_cd_segment_dim_id)
"seg1" btree (customer_srcid)
"seg2" btree (show_srcid)
"seg3" btree (season_srcid)
"seg4" btree (episode_srcid)
"seg5" btree (segment_srcid)
"sl_cd_segment_dim_ix1" btree (customer_srcid)
Any thoughts, suggestions, etc on how to improve performance for
this delete ?
Thanks in advance..
/Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
