Обсуждение: Postgresql concern of effect of invalid index

Logged by:          Freddie Burgess
Email address:     fburgess@radiantblue.com
PostgreSQL version: 9.3.4
Operating system:   Red_hat Linux 6.4
Description:

We purposely set the "indisvalid" flag to false to force the planner to cho=
ose one of the other indexes which executes more efficiently, we want to dr=
op the spatial index altogether, but because this partition table holds 14 =
billion rows it would take a long time to recreate all of the spatial index=
es, if one of our clients request an ad-hoc spatial query on historical dat=
a down the road.

My question is? What are the ramifications of having this spatial indexes r=
emain in this state?

thanks

update pg_index set indisvalid =3D false where indexrelid =3D 'sidx_sponser=
_report_y2014m06'::regclass;

Indexes:
    "rpi_sponser_report_y2014m06_pkey" PRIMARY KEY, btree (sponser_report_u=
id), tablespace "sponser_data_y2014"
    "idx_sessiondatetime_rpi_sponser_report_y2014m06" btree (session_uid, o=
rigin_date_time), tablespace "sponser_data_y2014"
    "idx_uuid_rpi_sponser_report_y2014m06" btree (sponser_report_uuid), tab=
lespace "sponser_data_y2014"
    "sidx_sponser_report_y2014m06" gist (sponser_location) INVALID, tablesp=
ace "sponser_data_y2014"

On Fri, Aug 22, 2014 at 10:27 PM, Burgess, Freddie <FBurgess@radiantblue.co=
m
> wrote:

> My question is? What are the ramifications of having this spatial indexes
> remain in this state?
>
> thanks
>
> update pg_index set indisvalid =3D false where indexrelid =3D
> '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,
--=20
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br n=C3=ADvel F!
www.dextra.com.br/postgres

Matheus de Oliveira <matioli.matheus@gmail.com> wrote:=0A> Burgess, Freddie=
 <FBurgess@radiantblue.com> wrote:=0A>=0A>> My question is? What are the ra=
mifications of having this=0A>> spatial indexes remain in this state?=0A=0A=
>> update pg_index set indisvalid =3D false=0A>>=A0=A0 where indexrelid =3D=
 'sidx_sponser_report_y2014m06'::regclass;=0A>=0A> If you marked it as inva=
lid, then it is not going to be updated=0A> anymore.=0A=0AI think you are c=
onfusing indisvalid with indisready:=0A=0Ahttp://www.postgresql.org/docs/cu=
rrent/interactive/catalog-pg-index.html=0A=0AFlagging it is invalid should =
suppress its use for queries, yet=0Astill do all the work of maintaining it=
.=0A=0A> BTW, you'd better take this to -performance list and discuss=0A> a=
bout why it is choosing a *bad* index instead of messing with=0A> the catal=
og.=0A=0A+1=0A=0AIn any event, this is most definitely *not* a bug report, =
so it=0Abelongs on a different list.=0A=0A--=0AKevin Grittner=0AEDB: http:/=
/www.enterprisedb.com=0AThe Enterprise PostgreSQL Company

On Sun, Aug 24, 2014 at 8:45 AM, Kevin Grittner <kgrittn@ymail.com> wrote:

> > If you marked it as invalid, then it is not going to be updated
> > anymore.
>
> I think you are confusing indisvalid with indisready:
>
> http://www.postgresql.org/docs/current/interactive/catalog-pg-index.html
>
> Flagging it is invalid should suppress its use for queries, yet
> still do all the work of maintaining it.



You are right. Sorry about the noise.

Regards,
--=20
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br n=C3=ADvel F!
www.dextra.com.br/postgres