Re: [MASSMAIL]long running delete

> Thank you for your quick response Gilberto. Much appreciated.
> I may very well follow your recommendation if I can determine if the
> current delete statement is not doing any work.
> Here is the statement and an explain:
>
> DELETE FROM contents USING contents AS c LEFT JOIN
> contents_social_posts csp ON csp.content_id = c.id LEFT JOIN
> social_posts
> sp ON sp.id = csp.social_post_id WHERE c.type = 'Content::Text::News'
> AND (csp.id IS NULL OR sp.id IS NULL);
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------
> Delete on contents (cost=1150608.37..2295927.20 rows=1 width=24)
> -> Nested Loop (cost=1150608.37..2295927.20 rows=1 width=24)
> -> Hash Right Join (cost=1150608.37..1173423.64 rows=1
> width=18)
> Hash Cond: (csp.content_id = c.id)
> Filter: ((csp.id IS NULL) OR (sp.id IS NULL))
> -> Hash Left Join (cost=6247.16..8286.32 rows=54372
> width=24)
> Hash Cond: (csp.social_post_id = sp.id)
> -> Seq Scan on contents_social_posts csp
> (cost=0.00..951.72 rows=54372 width=18)
> -> Hash (cost=4978.18..4978.18 rows=101518
> width=10)
> -> Seq Scan on social_posts sp
> (cost=0.00..4978.18 rows=101518 width=10)
> -> Hash (cost=1075343.73..1075343.73 rows=3970439
> width=10)
> -> Seq Scan on contents c (cost=0.00..1075343.73
> rows=3970439 width=10)
> Filter: ((type)::text =
> 'Content::Text::News'::text)
> -> Seq Scan on contents (cost=0.00..1059623.78 rows=6287978
> width=6)
> (14 rows)
>
> As you can see, many sequential scans especially 2 on the table we are
> performing the delete on

----Much index, about the table if general one problem

>
> Description of contents:
>
> \d contents
> Table "public.contents"
> Column | Type |
> Modifiers
> ---------------+-----------------------------+-------------------------------------------------------
> id | integer | not null default
> nextval('contents_id_seq'::regclass)
> raw_content | text |
> title | text |
> description | text |
> source_url | text |
> published_at | timestamp without time zone |
> guid | text |
> type | character varying(255) |
> created_at | timestamp without time zone |
> updated_at | timestamp without time zone |
> image_url | text |
> original_id | character varying(255) |
> refined_date | date |
> thumbnail_url | text |
> user_id | integer |
> flagged_at | timestamp without time zone |
> flagged_by | integer |
> removed_at | timestamp without time zone |
> removed_by | integer |
> category_id | integer |
> parent_id | integer |
> parent_type | character varying(255) |
> processing | boolean | default false
> sharable_id | integer |
> sharable_type | character varying(255) |
> meta_data | text |
> medium_url | text |
> pinned_at | date |
> pinned_until | date |
> banner_url | text |
> deleted_at | timestamp without time zone |
> Indexes:
> "primets_contents_pkey_id" PRIMARY KEY, btree (id), tablespace
> "prime2indexes"
> "primets_content_parent" btree (parent_id, parent_type), tablespace
> "prime2indexes"
> "primets_contents_category" btree (category_id), tablespace
> "prime2indexes"
> "primets_contents_desc_gin" gin (to_tsvector('english'::regconfig,
> description)), tablespace "prime2indexes"
> "primets_contents_guid" btree (guid), tablespace "prime2indexes"
> "primets_contents_pin_priority" btree
> ((GREATEST(refined_date::timestamp without time zone, pinned_until + '1
> day'::interval)), id), tablespace "prime2indexes"
> "primets_contents_refined_date_id" btree (refined_date, id),
> tablespace
> "prime2indexes"
> "primets_contents_sharable_id_sharable_type" btree (sharable_id,
> sharable_type), tablespace "prime2indexes"
> "primets_contents_source_gin" gin (to_tsvector('english'::regconfig,
> source_url)), tablespace "prime2indexes"
> "primets_contents_title_gin" gin (to_tsvector('english'::regconfig,
> title)), tablespace "prime2indexes"
> "primets_contents_type" btree (type), tablespace "prime2indexes"
> "primets_contents_user_id" btree (user_id), tablespace "prime2indexes"
> Triggers:
> _replication_logtrigger AFTER INSERT OR DELETE OR UPDATE ON contents
> FOR EACH ROW EXECUTE PROCEDURE _replication.logtrigger('_replication',
> '26', 'k')
> _replication_truncatetrigger BEFORE TRUNCATE ON contents FOR EACH
> STATEMENT EXECUTE PROCEDURE _replication.log_truncate('26')
> Disabled triggers:
> _replication_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON contents
> FOR EACH ROW EXECUTE PROCEDURE _replication.denyaccess('_replication')
> _replication_truncatedeny BEFORE TRUNCATE ON contents FOR EACH
> STATEMENT EXECUTE PROCEDURE _replication.deny_truncate()
>
>
>
> On Tue, Jun 28, 2016 at 1:15 PM, Gilberto Castillo <
> gilberto(dot)castillo(at)etecsa(dot)cu> wrote:
>
>>
>> > Good morning,
>> >
>> > We have been running a delete for nearly 24 hours now. I would like
>> to
>> > verify that it is either doing what it is supposed to do or 'spinning
>> its
>> > wheels'.
>> >
>> > We are running postgres 9.2.12.
>> >
>> > The delete statement is not waiting on any other transaction.
>> >
>> > I have run straces on the pid and I see lots of 'reads, lseeks, and an
>> > occasional semop. I have also looked in the base directory at the
>> file
>> > matched by the oid of the table (as defined in pg_class) and have seen
>> no
>> > change in size.
>> >
>> > Is there somewhere else I can verify that work is / is not being done?
>> > Perhaps looking for something else in strace?
>>
>>
>>
>> My recomendation,
>>
>> El DELETE es prohibitivo en Cualquier gestor de BD para ello ponga a su
>> concideración dos formas de como mejorar su comportamiento en PostgreSQL
>>
>> --Solución 1
>>
>> DELETE FROM string s WHERE NOT EXISTS (SELECT 1 FROM data d WHERE
>> d.object_id = s.id OR d.property_id = s.id OR d.value_id = s.id);
>>
>> --Solución 2
>>
>> (1) CREATE TABLE copia AS SELECT (...) WHERE (...)
>> (2) TRUNCATE en la tabla original.
>> (3) INSERT (...) SELECT (...) --actualizar desde la copia la tabla
>> original
>> (4) DROP TABLE copia.
>>
>>
>> >
>> > Thanks for your time.
>> >
>> > --
>> > *Mark Steben*
>> > Database Administrator
>> > @utoRevenue <http://www.autorevenue.com/> | Autobase
>> > <http://www.autobase.net/>
>> > CRM division of Dominion Dealer Solutions
>> > 95D Ashley Ave.
>> > West Springfield, MA 01089
>> > t: 413.327-3045
>> > f: 413.383-9567
>> >
>> > www.fb.com/DominionDealerSolutions
>> > www.twitter.com/DominionDealer
>> > www.drivedominion.com <http://www.autorevenue.com/>
>> >
>> > <http://autobasedigital.net/marketing/DD12_sig.jpg>
>> >
>>
>>
>> --
>> Saludos,
>> Gilberto Castillo
>> ETECSA, La Habana, Cuba
>>
>>
>
>
> --
> *Mark Steben*
> Database Administrator
> @utoRevenue <http://www.autorevenue.com/> | Autobase
> <http://www.autobase.net/>
> CRM division of Dominion Dealer Solutions
> 95D Ashley Ave.
> West Springfield, MA 01089
> t: 413.327-3045
> f: 413.383-9567
>
> www.fb.com/DominionDealerSolutions
> www.twitter.com/DominionDealer
> www.drivedominion.com <http://www.autorevenue.com/>
>
> <http://autobasedigital.net/marketing/DD12_sig.jpg>
>

--
Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba