Re: Way to avoid expensive Recheck Cond in index lookup?

"Matt Magoffin" <postgresql(dot)org(at)msqr(dot)us> writes:
> I also seemed to notice that after running a query that did return a large
> number of results where the query plan did use the text[] index, running
> the same query, or a similar one, would stop using the index lookup and
> just do a full table scan. Would that be the optimizer changing plans
> because of the statistics it gathered when it ran the query initially with
> the index lookup but found the re-check condition took such a long time to
> execute?

No, there is not any feed-forward from previous query executions to new
plans. (There's been discussion of that, but nothing done as yet.
Personally I'm worried that it's hard enough to understand what's
happening without any such effect.)

If you saw the plan changing for apparently no reason, maybe you have
autovacuum enabled? A background autovac could update the stored table
statistics and thereby cause a plan change.

> What I was trying to accomplish was to define a text[] index created from
> the results of an xpath() expression, for the purposes of being able to do
> fast index lookups using the && operator. But I'm finding that even when
> the index is used, the query is very slow and I was assuming it was coming
> from the re-check condition, which is defined as that xpath() call. So I'm
> finding that this approach isn't working out as I had hoped.

I'm not sure that anyone's done any performance analysis on xpath as
yet. Do you want to try oprofile or gprof or some other tool to see
where the time is going?

regards, tom lane