XML index

On 27 May 2010 12:22, Chris Roffler <croffler@earthlink.net> wrote:

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

I'd run an ANALYZE after inserting 5000 more rows. The stats will be
out of date.

Thom

Tried that .... same thing

On Thu, May 27, 2010 at 1:53 PM, Thom Brown <thombrown@gmail.com> wrote:

On 27 May 2010 12:22, Chris Roffler <croffler@earthlink.net> wrote:

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

I'd run an ANALYZE after inserting 5000 more rows. The stats will be
out of date.

Thom