Re: xpath index not being used

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