Way to avoid expensive Recheck Cond in index lookup?

Поиск
Список
Период
Сортировка
От Matt Magoffin
Тема Way to avoid expensive Recheck Cond in index lookup?
Дата
Msg-id 50134.192.168.1.108.1198013746.squirrel@msqr.us
обсуждение исходный текст
Ответы Re: Way to avoid expensive Recheck Cond in index lookup?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Hello, I'm trying to find a way to use a text[] index lookup using an
xpath() function in 8.3, but I suspect this situation is not specific to
8.3 or this exact query style. The query plan looks like

 Bitmap Heap Scan on lead  (cost=37.39..7365.22 rows=2206 width=8)
   Recheck Cond:
((xpath('/als:auto-lead-service/als:meta[@key="com.autoleadservice.TypeFlag"]/text()'::text,
xml, '{{als,http://autoleadservice.com/xml/als}}'::text[]))::text[] &&
'{foo,bar}'::text[])
   ->  Bitmap Index Scan on lead_type_flag_gin_idx  (cost=0.00..36.83
rows=2206 width=0)
         Index Cond:
((xpath('/als:auto-lead-service/als:meta[@key="com.autoleadservice.TypeFlag"]/text()'::text,
xml,
'{{als,http://autoleadservice.com/xml/als}}'::text[]))::text[] &&
'{foo,bar}'::text[])

The problem for me is, the Recheck Cond is then on the xpath() function
used by the function-based index. My understanding is that then the
database must actually call the xpath() function again on all matches from
the index lookup. Are there ways to re-write the query so the recheck
condition is not necessary? Or a way to define the index differently so
that I might be able to still compare text[] values from the index without
needing the recheck?

-- m@

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: combining semi-duplicate rows
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Way to avoid expensive Recheck Cond in index lookup?