Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> But it's true that it's possible for a slow expression to make the recheck
> very expensive. The planner doesn't have a very good understanding of how
> to
> tell whether the expression is likely to be slow.
>
> The case I ran into is thing like "WHERE x = ANY $1::integer[]" which
> become
> very slow for very large arrays. So I'm sure xpath() could possibly
> trigger
> the same case.
>
> But the number of matching pages would have to be quite large. And in that
> case the alternative (regular index scans) is going to suck too.

So the actual index function expression is _only_ evaluated in the
re-check for some (or all?) matches, if there are more matching pages than
can fit into the memory allocated by work_mem?

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?

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.

-- m@

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux