Postgresql concern of effect of invalid index

From: "Burgess, Freddie" <FBurgess(at)Radiantblue(dot)com>
To: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Postgresql concern of effect of invalid index
Date: 2014-08-23 01:27:51
Message-ID: 3BBE635F64E28D4C899377A61DAA9FE03F08FB57@NBSVR-MAIL01.radiantblue.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


Logged by: Freddie Burgess
Email address: fburgess(at)radiantblue(dot)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 choose one of the other indexes which executes more efficiently, we want to drop 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 indexes, if one of our clients request an ad-hoc spatial query on historical data down the road.

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;

Indexes:
"rpi_sponser_report_y2014m06_pkey" PRIMARY KEY, btree (sponser_report_uid), tablespace "sponser_data_y2014"
"idx_sessiondatetime_rpi_sponser_report_y2014m06" btree (session_uid, origin_date_time), tablespace "sponser_data_y2014"
"idx_uuid_rpi_sponser_report_y2014m06" btree (sponser_report_uuid), tablespace "sponser_data_y2014"
"sidx_sponser_report_y2014m06" gist (sponser_location) INVALID, tablespace "sponser_data_y2014"

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Burgess, Freddie 2014-08-23 01:32:33 PostgreSQL I/O bottleneck
Previous Message Tom Lane 2014-08-21 19:55:51 Re: BUG #11207: empty path will segfault jsonb #>