How do I find out why CREATE INDEX CONCURRENTLY is failing?



  • I set off the creation of an index with the command

    CREATE INDEX CONCURRENTLY index_Name ON namespace.table_name(column_name);
    

    and watched the action using this command on another session:

    select now()::time(0), a.query, p.phase, p.blocks_total, p.blocks_done, p.tuples_total, p.tuples_done 
    from pg_stat_progress_create_index p 
      join pg_stat_activity a on p.pid = a.pid 
    \watch 2
    

    After a while, the index creation stopped as seen in these two subsequent watch calls. The first one shows progress, and the subsequent one is empty. This indicates that the index build shut down between these consecutive 4-minute calls.

    now query phase blocks_total blocks_done tuples_total tuples_done
    21:17:26 CREATE INDEX CONCURRENTLY any_transfers_to_address_idx ON ethereum.any_transfers(to_address); building index: scanning table 85807113 30279326 0 0
    now query phase blocks_total blocks_done tuples_total tuples_done

    Is there a way I can find out exactly why this would happen? Are there logs I can query? Any and all suggestions welcome, thanks.



  • Look into the database log. If you left the default configuration, you will see a log entry if CREATE INDEX was terminated by an error (for example, through statement_timeout). If it failed, remove the INVALID index on the table that is left behind in that case.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2