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@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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.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 ?
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!
