Hi.
I was instructed to delete old records from one of the tables in our production
system. The deletion took hours and I had to stop it in mid-operation and
reschedule it as a night job. But then I had to do the same when I got up in
the morning and it was still running.
The odd thing about it: There are 4720965 records in the table, of which I have
to delete 3203485. This should not have taken too long, and the EXPLAIN
estimate for it seemed to agree with me:
bcentral=> explain delete
from subscriptions s
where (date_registered < '2011-11-13' and operator <> 'P') and service_id not
in ( select id from alerts_services )
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Delete (cost=38885.86..155212.37 rows=1630425 width=6)
-> Bitmap Heap Scan on subscriptions s (cost=38885.86..155212.37
rows=1630425 width=6)
Filter: ((date_registered < '2011-11-13 00:00:00'::timestamp without
time zone) AND (operator <> 'P'::bpchar) AND (NOT (hashed SubPlan 1)))
-> Bitmap Index Scan on t_ind (cost=0.00..38473.03 rows=2361115
width=0)
Index Cond: ((date_registered < '2011-11-13 00:00:00'::timestamp
without time zone) = true)
SubPlan 1
-> Seq Scan on alerts_services (cost=0.00..4.58 rows=258 width=4)
(7 rows)
I got an interesting clue, though, when I canceled the deletion the second time
around. I got the following error message:
Cancel request sent
ERROR: canceling statement due to user request
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."sent_messages" x WHERE $1
OPERATOR(pg_catalog.=) "subscription_id" FOR SHARE OF x"
As you can see in the EXPLAIN sentence, I'm trying to delete from a table
called "subscriptions", and this context is in another table called
"sent_messages" which is related to it by foreign key. Now, I'd say that it was
waiting to get a lock on the "sent_messages" table (from which I duly removed
the related records before running my delete), and that I should have known
that. Only, I was using another connection to monitor pg_stat_activity while
the delete is done, and the delete process had "false" in the "waiting" column!
bcentral=# SELECT usename, procpid, query_start, client_addr, client_port,
current_query, waiting
FROM pg_stat_activity
WHERE query_start < now() - interval '3 seconds'
AND xact_start is not null order by xact_start;
-[ RECORD 1
]-+------------------------------------------------------------------------------------------------
--------------------
usename | bcentral
procpid | 20047
query_start | 2011-11-29 02:01:28.968161+02
client_addr | 192.168.34.34
client_port | 55709
current_query | delete
: from subscriptions s
: where (date_registered < '2011-11-13' and operator <> 'P') and
service_id not in ( select id fr
om alerts_services )
: ;
waiting | f
Um... so what gives? What's happening here? The server is PostgreSQL 9.0.4.
TIA,
Herouth
