Re: XML Index again

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:737,4b9389db296924445911763!