3

I need to insert a new two-field index on an existing table with about 600 million rows in it. The table contains data accumulated over about a year with approximately 2 million new rows added per day.

I know adding a new index to one very large table in one go will take ages, so to save time I started by only indexing the last few days worth of data using the following command…

CREATE INDEX  idx_001
ON message (deviceNumber, messageTime)
WHERE messageTime >='07-May-2018 00:00:00' ;

This took about 3 hours, or about 40 minutes to index one day.

Ideally I’d like to index the whole table, but at 40 minutes for a days data doing it on one go will take over a week.

I guess I could create lots of small indexes, each for a few days of data, maybe something like this…

CREATE INDEX  idx_002
ON message (deviceNumber, messageTime)
WHERE messageTime <'07-May-2018 00:00:00' AND messageTime >='01-May-2018 00:00:00'  ;

But I’d prefer to stick with one index. Is there any way of ALTERing the where clause of the existing index to increase the days covered? Or do I have to create lots of small indexes?

6
  • 1
    In addition to @FedericoRazzoli's answer, that seems like a ridiculous amount of time to index 9 million rows. You should probably investigate why it takes so long.
    – jjanes
    May 13, 2018 at 1:56
  • 1
    you're keeping this data forever? if not this may be a good time to consider partitioning using sql inheritance.
    – Jasen
    May 13, 2018 at 3:11
  • Why are you using these (weirdly formatted) strings, ie. '07-May-2018 00:00:00' and not using a standard format, like '2018-05-07 00:00:00'? May 13, 2018 at 10:23
  • @ypercube - explicitly stating 'May' leaves no room for error. Using '2018-05-07' allows for potential confusion between american/european users who follow different conventions. May 13, 2018 at 10:44
  • 1
    @ypercube. Not everyone uses ISO standard format. See postgresql.org/docs/current/static/… for some other date formats. The characters 'May' are unlikely to mistaken for any other month. May 13, 2018 at 13:30

1 Answer 1

6

No, you cannot alter an index. Because, even if PostgreSQL allowed this, it would need to rebuild the index anyway. Unless it implemented very smart checks on the WHERE clause, which would serve quite rare use cases.

Having many indexes is also not optimal. Every time you run a query, the planner has to consider all existing indexes and pick up the best one. If you have several indexes, the planner takes more time. Not ages - but still, this delay affects each and every query involving that table.

My suggestion is... be patient and build a big index. You can use CREATE INDEX CONCURRENTLY to avoid locks.

Your Answer

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

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