Обсуждение: xpath index not being used

Hello,

I've searched the mailing list and I tried using defining a xpath index per the post, but my query is still not using it.

Essentially, I'm storing a fragment of xml and I want to create xpath indexes on them.

The two rows I'm going to insert look like this (alex & bob are the only names repeated):
<names><name>frank</name><name>mason</name><name>bob</name><name>alex</name></names>
<names><name>alex</name><name>bob</name><name>cola</name><name>doda</name></names>

create table test (data xml);
CREATE TABLE

CREATE INDEX name_test ON test (((xpath('//names/name/text()', data))[1]::text));
CREATE INDEX

I can select with a where clause without issue:
select * from test where ((xpath('//names/name[. ="bob"]/text()', data))[1]::text) = 'bob';
                                         data                                        
--------------------------------------------------------------------------------------
 <names><name>alex</name><name>bob</name><name>cola</name><name>doda</name></names>
 <names><name>frank</name><name>mason</name><name>bob</name><name>alex</name></names>
(2 rows)


However, when I check which index it's using, it's not using the xpath index:
explain select * from test where ((xpath('//names/name[. ="bob"]/text()', data))[1]::text) = 'bob';
                                               QUERY PLAN                                               
---------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1.03 rows=1 width=32)
   Filter: (((xpath('//names/name[. ="bob"]/text()'::text, data, '{}'::text[]))[1])::text = 'bob'::text)
(2 rows)


Any help on what I'm doing wrong would be appreciated.

On 19/07/10 05:41, Irooniam wrote:

> However, when I check which index it's using, it's not using the xpath
> index:
> explain select * from test where ((xpath('//names/name[. ="bob"]/text()',
> data))[1]::text) = 'bob';
>                                                QUERY
> PLAN
> ---------------------------------------------------------------------------------------------------------
>  Seq Scan on test  (cost=0.00..1.03 rows=1 width=32)
>    Filter: (((xpath('//names/name[. ="bob"]/text()'::text, data,
> '{}'::text[]))[1])::text = 'bob'::text)
> (2 rows)
>
>
> Any help on what I'm doing wrong would be appreciated.

Your data is too trivial. Pg thinks it'll be faster to do a seq scan and
filter than use the index to do the work. It'd be more helpful if you
can post EXPLAIN ANALYZE output from your real data.

AFAIK the planner isn't very good at factoring in function execution
costs and number of function executions required when choosing between
index use and filtered seqscans.

--
Craig Ringer