Re: Postgresql concern of effect of invalid index

On Fri, Aug 22, 2014 at 10:27 PM, Burgess, Freddie <FBurgess(at)radiantblue(dot)com
> wrote:

> My question is? What are the ramifications of having this spatial indexes
> remain in this state?
>
> thanks
>
> update pg_index set indisvalid = false where indexrelid =
> 'sidx_sponser_report_y2014m06'::regclass;

If you marked it as invalid, then it is not going to be updated anymore. If
you want it to be used by some query down the road, you'll have to REINDEX
it, and REINDEX will take same time as building a new index. Can even be
worst, as it will lock the table, as there is not yet REINDEX CONCURRENTLY,
but there is CREATE INDEX CONCURRENTLY.

BTW, you'd better take this to -performance list and discuss about why it
is choosing a *bad* index instead of messing with the catalog.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres