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 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"
Import Notes
Reply to msg id not found: 3BBE635F64E28D4C899377A61DAA9FE03F08FB17@NBSVR-MAIL01.radiantblue.localReference msg id not found: 3BBE635F64E28D4C899377A61DAA9FE03F08FB17@NBSVR-MAIL01.radiantblue.local
On Fri, Aug 22, 2014 at 10:27 PM, Burgess, Freddie <FBurgess@radiantblue.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
Matheus de Oliveira <matioli.matheus@gmail.com> wrote:
Burgess, Freddie <FBurgess@radiantblue.com> wrote:
My question is? What are the ramifications of having this
spatial indexes remain in this state?
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.
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.
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.
+1
In any event, this is most definitely *not* a bug report, so it
belongs on a different list.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres