2

I'm using PostgreSQL 9.6.1 on Amazon RDS

I'm adding a CHECK constraint on an already indexed column named timestamp_ of a table called event_click that contains about 1 million rows.

ALTER TABLE event_click_2016_8
ADD CONSTRAINT click_2016_8_chk CHECK ( timestamp_ >= '2016-8-01' AND timestamp_ < '2016-9-01' );

The above takes 15 minutes to complete. Why is it so slow? What specifically is holding up this operation? There is nothing else running on the DB and no locks being held by other processes.

r3.large instance (2 vCPU, 15 GiB RAM)
maintenance_work_mem = 256147
work_mem = 1024000
4
  • 3
    Checking a million rows shouldn't take 15 minutes. Maybe it was waiting for a lock on the table. The Postgres Wiki contains some statement where you can monitor locks: wiki.postgresql.org/wiki/Lock_Monitoring
    – user1822
    Apr 27, 2017 at 6:16
  • This is something that occurred to me and I ensure that there is nothing blocking this query (no lock waits). Apr 27, 2017 at 14:11
  • 1
    It is reproducible? I can add such a constraint to a larger table in under a second.
    – jjanes
    Apr 27, 2017 at 15:59
  • I'm having a hard time reproducing it. Maybe amazon IO flakyness Apr 27, 2017 at 19:07

1 Answer 1

3

when creating the constraint, check pg_locks for granted is false or pg_stat_activity for wait_event_type and wait_event where pid = <pid that adds the check> (note that these two columns are new in 9.6 - in earlier versions you see only the fact that it is waiting).

Most probably your ALTER TABLE was waiting for other process to release a lock before it could acquire exclusive one.

2
  • this was not the case. I updated my question to reflect that Apr 27, 2017 at 15:04
  • well you can try running timestamp_ >= '2016-8-01' AND timestamp_ < '2016-9-01' before creating constraint, if it takes 15 minutes thet is it IO?..
    – Vao Tsun
    Apr 27, 2017 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.