Re: XML index

[Chris Roffler <croffler@xxxxxxxxxxxxx>]

Changed the create index statement to :   USING hash  and it seems to work.

Any idea why btree does not work ?

Thanks

Chris

On Thu, May 27, 2010 at 3:47 PM, Chris Roffler <croffler@xxxxxxxxxxxxx> wrote:

Tried that .... same thing 

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

On 27 May 2010 12:22, Chris Roffler <croffler@xxxxxxxxxxxxx> 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