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@
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 |