I have a table with an xml column, created an index as follows:
CREATE INDEX xml_index
ON test
USING btree
(((xpath('//*/ChangedBy/text()'::text, external_attributes))[1]::text));
And here is my select statement:
Select uuid from t
where (xpath('//*/ChangedBy/text()', external_attributes))[1]::text = 'User';
I then insert 100rows into this table, then do a select with the above statement.
Explain shows that the query is using the xml_index.
Now I insert 5000 more rows and Explain shows that the query does not use the xml_index anymore.
However, if I drop the index and re create it, then Explain tells me that it's using the index again.
Any ideas what is going on here ?
Thanks
Chris