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"
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 #> |