Re: 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