From: Tom Lane <tgl@sss.pgh.pa.us>
To: Massalin Yerzhan <yerzhik@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Simple delete query is taking too long (never ends)
Date: Wed, 11 Nov 2015 14:33:35 -0500
Message-ID: <17089.1447270415@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMAXSpJyE4TN068xzDNXk-=7AJBnmZVrK8D2ZQz0eoP0vo30CQ@mail.gmail.com>
References: <CAMAXSpJyE4TN068xzDNXk-=7AJBnmZVrK8D2ZQz0eoP0vo30CQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Massalin Yerzhan <yerzhik@gmail.com> writes:
> I'm having an issue. The query never ends:
> delete from bb_gamelist_league;
9 times out of 10, the answer to this type of problem is that you have
some table referencing this one by a foreign key, and the referencing
column is not indexed. PG doesn't require such an index, but lack of
one will mean that retail checks or deletions of referencing rows are
really slow.
If you're not sure which table is the problem, try doing an EXPLAIN
ANALYZE of a DELETE that will only remove a few rows. You should
see some time blamed on a trigger associated with the FK constraint.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, yerzhik@gmail.com
Subject: Re: Simple delete query is taking too long (never ends)
In-Reply-To: <17089.1447270415@sss.pgh.pa.us>
* 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