pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple delete query is taking too long (never ends)
9+ messages / 5 participants
[nested] [flat]
* Simple delete query is taking too long (never ends)
@ 2015-11-11 19:09 Massalin Yerzhan <yerzhik@gmail.com>
2015-11-11 19:33 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Massalin Yerzhan @ 2015-11-11 19:09 UTC (permalink / raw)
To: pgsql-performance
Postgresql version 9.4.4.
I'm having an issue. The query never ends:
delete from bb_gamelist_league;
No WHERE clause used. There are approx. 227000 rows in that table.
Here is the table itself:
CREATE TABLE bb_gamelist_league (
id SERIAL NOT NULL ,
bb_league_id INTEGER NOT NULL ,
day_number INTEGER,
date BIGINT ,
team_id1 INTEGER ,
team_id2 INTEGER ,
score1 SMALLINT ,
score2 SMALLINT ,
attended_people INTEGER ,
is_play_off BOOL ,
play_off_code VARCHAR(5),
game_status BOOL ,
is_finished BOOL ,
was_taken_by_gameserv BOOL,
taken_by_coordinator_status BOOL,
seed TIMESTAMP,
managerA_watching BOOL,
managerB_watching BOOL,
day_period VARCHAR(10),
group_number VARCHAR(30),
PRIMARY KEY(id) ,
FOREIGN KEY(bb_league_id) REFERENCES bb_league(id),
FOREIGN KEY (team_id1) REFERENCES bb_team_info(id),
FOREIGN KEY (team_id2) REFERENCES bb_team_info(id));
There are some indexes on that table:
public | bb_gamelist_league | bb_gamelist_league_fkindex1 |
| CREATE INDEX bb_gamelist_league_fkindex1 ON bb_gamelist_league USING
btree (bb_league_id)
public | bb_gamelist_league | bb_gamelist_league_pkey |
| CREATE UNIQUE INDEX bb_gamelist_league_pkey ON bb_gamelist_league USING
btree (id)
Also explain gives the following result:
explain delete from bb_gamelist_league;
QUERY PLAN
--------------------------------------------------------------------------------
Delete on bb_gamelist_league (cost=0.00..6954.63 rows=281363 width=6)
-> Seq Scan on bb_gamelist_league (cost=0.00..6954.63 rows=281363
width=6)
(2 rows)
Explain analyze never ends (because the query itself is never ending).
I checked the locks: there are no locks on tables.
The CPU is fast enough but "top" command on linux shows 100% load for
postgres process.
Could you help to resolve the issue?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Simple delete query is taking too long (never ends)
2015-11-11 19:09 Simple delete query is taking too long (never ends) Massalin Yerzhan <yerzhik@gmail.com>
@ 2015-11-11 19:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-11-12 15:12 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2015-11-11 19:33 UTC (permalink / raw)
To: Massalin Yerzhan <yerzhik@gmail.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Simple delete query is taking too long (never ends)
2015-11-11 19:09 Simple delete query is taking too long (never ends) Massalin Yerzhan <yerzhik@gmail.com>
2015-11-11 19:33 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-11-12 15:12 ` Merlin Moncure <mmoncure@gmail.com>
2015-11-12 15:36 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
2015-11-12 15:48 ` Re: Simple delete query is taking too long (never ends) Craig James <cjames@emolecules.com>
0 siblings, 2 replies; 9+ messages in thread
From: Merlin Moncure @ 2015-11-12 15:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Massalin Yerzhan <yerzhik@gmail.com>; pgsql-performance
On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
You've answered this question (with the same answer) what feels like a
gazillion times. I guess the underlying problem is that EXPLAIN is,
uh, not explaining things very well.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Simple delete query is taking too long (never ends)
2015-11-11 19:09 Simple delete query is taking too long (never ends) Massalin Yerzhan <yerzhik@gmail.com>
2015-11-11 19:33 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
2015-11-12 15:12 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
@ 2015-11-12 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2015-11-12 15:36 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Massalin Yerzhan <yerzhik@gmail.com>; pgsql-performance
Merlin Moncure <mmoncure@gmail.com> writes:
> On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
> You've answered this question (with the same answer) what feels like a
> gazillion times. I guess the underlying problem is that EXPLAIN is,
> uh, not explaining things very well.
In principle, a plain EXPLAIN could list the triggers that would
potentially be fired by the query, but I'm afraid that wouldn't help
much. The actual performance problem is down inside the trigger,
which is an opaque black box so far as EXPLAIN can possibly know.
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Simple delete query is taking too long (never ends)
2015-11-11 19:09 Simple delete query is taking too long (never ends) Massalin Yerzhan <yerzhik@gmail.com>
2015-11-11 19:33 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
2015-11-12 15:12 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
@ 2015-11-12 15:48 ` Craig James <cjames@emolecules.com>
2015-11-12 22:07 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Craig James @ 2015-11-12 15:48 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Massalin Yerzhan <yerzhik@gmail.com>; pgsql-performance
On Thu, Nov 12, 2015 at 7:12 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 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.
>
> You've answered this question (with the same answer) what feels like a
> gazillion times. I guess the underlying problem is that EXPLAIN is,
> uh, not explaining things very well.
>
What about a warning on creation?
db=> create table foo(i integer primary key);
db=> create table bar(j integer primary key, i integer);
db=> alter table bar add constraint fk_bar foreign key(i) references foo(i);
WARNING: fk_bar: column bar(i) has no index, deletions on table foo may be
slow.
It might save some fraction of these questions.
Craig
>
> merlin
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Simple delete query is taking too long (never ends)
2015-11-11 19:09 Simple delete query is taking too long (never ends) Massalin Yerzhan <yerzhik@gmail.com>
2015-11-11 19:33 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
2015-11-12 15:12 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
2015-11-12 15:48 ` Re: Simple delete query is taking too long (never ends) Craig James <cjames@emolecules.com>
@ 2015-11-12 22:07 ` Merlin Moncure <mmoncure@gmail.com>
2015-11-12 22:26 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Merlin Moncure @ 2015-11-12 22:07 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Massalin Yerzhan <yerzhik@gmail.com>; pgsql-performance
On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames@emolecules.com> wrote:
>
> On Thu, Nov 12, 2015 at 7:12 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>
>> On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> > 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.
>>
>> You've answered this question (with the same answer) what feels like a
>> gazillion times. I guess the underlying problem is that EXPLAIN is,
>> uh, not explaining things very well.
>
>
> What about a warning on creation?
>
> db=> create table foo(i integer primary key);
> db=> create table bar(j integer primary key, i integer);
> db=> alter table bar add constraint fk_bar foreign key(i) references foo(i);
> WARNING: fk_bar: column bar(i) has no index, deletions on table foo may be
> slow.
>
> It might save some fraction of these questions.
Maybe, but I wonder if this would cause pg_restore to bleat warnings
when restoring. I was hoping that explain could report potential
irregularities, but Tom's comments seem to suggest difficulties there.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Simple delete query is taking too long (never ends)
2015-11-11 19:09 Simple delete query is taking too long (never ends) Massalin Yerzhan <yerzhik@gmail.com>
2015-11-11 19:33 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
2015-11-12 15:12 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
2015-11-12 15:48 ` Re: Simple delete query is taking too long (never ends) Craig James <cjames@emolecules.com>
2015-11-12 22:07 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
@ 2015-11-12 22:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-11-13 14:15 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2015-11-12 22:26 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Craig James <cjames@emolecules.com>; Massalin Yerzhan <yerzhik@gmail.com>; pgsql-performance
Merlin Moncure <mmoncure@gmail.com> writes:
> On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames@emolecules.com> wrote:
>> What about a warning on creation?
>>
>> db=> create table foo(i integer primary key);
>> db=> create table bar(j integer primary key, i integer);
>> db=> alter table bar add constraint fk_bar foreign key(i) references foo(i);
>> WARNING: fk_bar: column bar(i) has no index, deletions on table foo may be
>> slow.
>>
>> It might save some fraction of these questions.
> Maybe, but I wonder if this would cause pg_restore to bleat warnings
> when restoring.
We could probably teach pg_dump to put index definitions before FKs, if it
doesn't already. But I'm suspicious of this sort of "training wheels"
warning --- we've had roughly similar messages in the past and removed
them because too many people complained about them.
Worth noting in particular is that there would be no way to avoid the
warning unless you split out the FK declaration to a separate "alter table
add constraint" step. pg_dump does that anyway, but manual schema
definitions likely would look more like
create table foo(i integer primary key);
create table bar(j integer primary key, i integer references foo);
create index on bar(i);
which would provoke the warning. I fear a warning like that would have
a very short life expectancy.
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Simple delete query is taking too long (never ends)
2015-11-11 19:09 Simple delete query is taking too long (never ends) Massalin Yerzhan <yerzhik@gmail.com>
2015-11-11 19:33 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
2015-11-12 15:12 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
2015-11-12 15:48 ` Re: Simple delete query is taking too long (never ends) Craig James <cjames@emolecules.com>
2015-11-12 22:07 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
2015-11-12 22:26 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-11-13 14:15 ` Merlin Moncure <mmoncure@gmail.com>
2015-11-13 15:51 ` Re: Simple delete query is taking too long (never ends) Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Merlin Moncure @ 2015-11-13 14:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Craig James <cjames@emolecules.com>; Massalin Yerzhan <yerzhik@gmail.com>; pgsql-performance
On Thu, Nov 12, 2015 at 4:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames@emolecules.com> wrote:
>>> What about a warning on creation?
>>>
>>> db=> create table foo(i integer primary key);
>>> db=> create table bar(j integer primary key, i integer);
>>> db=> alter table bar add constraint fk_bar foreign key(i) references foo(i);
>>> WARNING: fk_bar: column bar(i) has no index, deletions on table foo may be
>>> slow.
>>>
>>> It might save some fraction of these questions.
>
>> Maybe, but I wonder if this would cause pg_restore to bleat warnings
>> when restoring.
>
> We could probably teach pg_dump to put index definitions before FKs, if it
> doesn't already. But I'm suspicious of this sort of "training wheels"
> warning --- we've had roughly similar messages in the past and removed
> them because too many people complained about them.
For posterity, indexes are the last step -- and I think that's a good
way to do things. As to the broader point, I agree. Warnings should
be reserved for things that are demonstrably dubious, and there are
just too many situations where that doesn't apply for an unindexed
foreign constraint. Oh well.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Simple delete query is taking too long (never ends)
2015-11-11 19:09 Simple delete query is taking too long (never ends) Massalin Yerzhan <yerzhik@gmail.com>
2015-11-11 19:33 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
2015-11-12 15:12 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
2015-11-12 15:48 ` Re: Simple delete query is taking too long (never ends) Craig James <cjames@emolecules.com>
2015-11-12 22:07 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
2015-11-12 22:26 ` Re: Simple delete query is taking too long (never ends) Tom Lane <tgl@sss.pgh.pa.us>
2015-11-13 14:15 ` Re: Simple delete query is taking too long (never ends) Merlin Moncure <mmoncure@gmail.com>
@ 2015-11-13 15:51 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2015-11-13 15:51 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Craig James <cjames@emolecules.com>; Massalin Yerzhan <yerzhik@gmail.com>; pgsql-performance
On Fri, Nov 13, 2015 at 7:15 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Thu, Nov 12, 2015 at 4:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Merlin Moncure <mmoncure@gmail.com> writes:
>>> On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames@emolecules.com> wrote:
>>>> What about a warning on creation?
>>>>
>>>> db=> create table foo(i integer primary key);
>>>> db=> create table bar(j integer primary key, i integer);
>>>> db=> alter table bar add constraint fk_bar foreign key(i) references foo(i);
>>>> WARNING: fk_bar: column bar(i) has no index, deletions on table foo may be
>>>> slow.
>>>>
>>>> It might save some fraction of these questions.
>>
>>> Maybe, but I wonder if this would cause pg_restore to bleat warnings
>>> when restoring.
>>
>> We could probably teach pg_dump to put index definitions before FKs, if it
>> doesn't already. But I'm suspicious of this sort of "training wheels"
>> warning --- we've had roughly similar messages in the past and removed
>> them because too many people complained about them.
>
> For posterity, indexes are the last step -- and I think that's a good
> way to do things. As to the broader point, I agree. Warnings should
> be reserved for things that are demonstrably dubious, and there are
> just too many situations where that doesn't apply for an unindexed
> foreign constraint. Oh well.
If they were implemented as a notice that would be different. Much
like the notice you get about index creation on PK / Unique constraint
creation. But I'm not 100% sure it's a good idea.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2015-11-13 15:51 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-11 19:09 Simple delete query is taking too long (never ends) Massalin Yerzhan <yerzhik@gmail.com>
2015-11-11 19:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-11-12 15:12 ` Merlin Moncure <mmoncure@gmail.com>
2015-11-12 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-11-12 15:48 ` Craig James <cjames@emolecules.com>
2015-11-12 22:07 ` Merlin Moncure <mmoncure@gmail.com>
2015-11-12 22:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-11-13 14:15 ` Merlin Moncure <mmoncure@gmail.com>
2015-11-13 15:51 ` Scott Marlowe <scott.marlowe@gmail.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