19

I have a Postgres table with 26 million rows in it, and I want to add an indexed boolean column.

Is there a way to calculate how long that type of operation will take, before attempting it in production?

Carolyn Conway
  • 1,356
  • 1
  • 15
  • 21
  • 3
    Why do you want to index a boolean column? That won't provide a huge increase in performance as a boolean only two options are true / false vs something unique-ish that would be improved by an index. – mituw16 Aug 17 '16 at 18:01
  • I wanted to index the boolean column only because I assumed it would improve performance. Do you have any resources or links either saying (1) that indices on boolean columns don't improve performance, or (2) describing how to know if an index will help? – Carolyn Conway Aug 17 '16 at 18:10
  • 1
    This is for mysql, but the same idea applies. You will see almost no speed improvement in your queries by indexing a boolean column. http://stackoverflow.com/a/1845014/1729859 Also http://stackoverflow.com/a/10524734/1729859 – mituw16 Aug 17 '16 at 18:15
  • 1
    Hi Carolyn, In the end did you undertake this index creation? If so, how long did it take? – Scary Wombat Mar 31 '17 at 04:59
  • @ScaryWombat If I'm remembering correctly, I think it only took a minute or two to run. Unfortunately I still haven't found a good way to estimate that type of thing in advance ¯\_(ツ)_/¯ – Carolyn Conway Mar 31 '17 at 17:13
  • I created a unique index on a char field (postgres aurora 10.14) and it took around 4mins for 24m rows. – Matthew Hegarty Oct 23 '21 at 11:09

1 Answers1

13

There is no way to calculate index time as it depends on many factors (hardware, software, configuration, load, etc.)

The good news is that you may CREATE INDEX CONCURRENTLY, which takes longer but does not lock the table. So the operation has no side effects except higher load on server.

For some caveats, check the documentation.

jsfan
  • 1,373
  • 9
  • 21
user3621424
  • 146
  • 1
  • 2