Thread: XML index
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
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:
I'd run an ANALYZE after inserting 5000 more rows. The stats will beOn 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
>
out of date.
Thom
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@earthlink.net> wrote:
Tried that .... same thingOn Thu, May 27, 2010 at 1:53 PM, Thom Brown <thombrown@gmail.com> wrote:I'd run an ANALYZE after inserting 5000 more rows. The stats will beOn 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
>
out of date.
Thom