Way to avoid expensive Recheck Cond in index lookup?

From: "Matt Magoffin" <postgresql(dot)org(at)msqr(dot)us>
To: pgsql-general(at)postgresql(dot)org
Subject: Way to avoid expensive Recheck Cond in index lookup?
Date: 2007-12-18 21:35:46
Message-ID: 50134.192.168.1.108.1198013746.squirrel@msqr.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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[(at)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[(at)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@

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-12-18 22:24:06 Re: Way to avoid expensive Recheck Cond in index lookup?
Previous Message Tom Lane 2007-12-18 21:28:51 Re: combining semi-duplicate rows