Re: XML Index again

Alban

Thanks for your help, your suggestion worked.

I need another xpath expression to find any Attribute with Name ="xxxx"
under the Attributes node. (not just if it is in a specific position)
see query below.
How do I create an index for this xpath expression ?

Thanks
Chris

SELECT * FROM time_series
WHERE
array_upper((xpath('/AttributeList/Attributes/Attribute[Name="xxxxx"]',
external_attributes)),1) > 0

On Sun, Mar 7, 2010 at 1:11 PM, Alban Hertroys <
dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:

> On 7 Mar 2010, at 11:02, Chris Roffler wrote:
>
> > I still have some problems with my xml index
> >
> > CREATE INDEX xml_index
> > ON time_series
> > USING btree
> > ((
> > (xpath('/AttributeList/Attributes/Attribute/Name/text()',
> external_attributes))[1]::text));
> >
> > When I run the following query the index is not used :
> >
> > select id from time_series where
> > array_upper(
> > (xpath('/AttributeList/Attributes/Attribute[Name="Attribute122021"]',
> external_attributes))
> > , 1) > 0
> >
> > Any Idea on how to configure the index ?
>
> There are a couple of cases where Postgres won't use your index, but in
> this case it's quite clearly because you're asking for (quite) a different
> expression than the one you indexed.
>
> You seem to want to test for the existence of nodes with a specific name,
> maybe this is what you're looking for?:
>
> SELECT id FROM time_series t1 WHERE EXISTS (
> SELECT 1
> FROM time_series t2
> WHERE (xpath('/AttributeList/Attributes/Attribute/Name/text()',
> external_attributes))[1]::text = ('Attribute122021', external_attributes)
> AND t2.id = t1.id
> );
>
> It's just a guess at what you're trying to do, so I may very well have
> gotten it wrong. The important part is that you need to use the expression
> you indexed in your where clause, or the database has no idea you mean
> something similar as to what you indexed.
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:1034,4b9389d6296921789322580!
>
>
>