Postgres – Should I Drop Indexes Before Changing Column Datatype?

If you have an index on created then the planner will need to choose between an using that index or the PK (or a full table scan) - it will not benefit from both at the same time.

--EDIT

As pointed out by @jug in the comments below, this is not accurate at least since 8.1: the planner may choose to build two in-memory bitmaps and combine them to get the result set. This gets more expensive as the tables get bigger, so the planner may choose not to do this depending on the size of the table and the estimated cost of using one index and then filtering.

--END EDIT

The new index will only be helpful if in some cases using it is more efficient than access via the PK. The kind of things that could make this likely include:

• A large number of (...) in SELECT * FROM foo WHERE foo_id IN (...) AND created > 1234 AND created <= 6789
• A small range, eg created > 6780 AND created <= 6790

Unless one or both is likely to happen, you should not create the secondary index - if they might, it would be best to test each scenario with and without the index to see if any performance benefit is worth the cost (eg increased storage and overhead for insert and update operation)

I found solution making creating index quite faster... (on Oracle 11g works fine)

drop index I_EMPLOYEE_SALARY_T;
create index I_EMPLOYEE_SALARY_T on EMPLOYEE (SALARY, DEPARTAMENT) 
tablespace IDX_TABLESPACE local unusable;

Then I need to rebuild index online choosing partition:

ALTER INDEX I_EMPLOYEE_SALARY_T REBUILD PARTITION partition_name online;

Altering index is much faster because it using different, faster mechanism and data can be accessed much sooner. For example when data for employees (in this example) is partitioned by timestamp. So newer data are more quickly available.