Re: No heap lookups on index

Tom Lane wrote:

>What sort of problems are you dealing with exactly? There has been
>some discussion of changes that would improve certain scenarios. For
>instance it might be plausible to do joins using index information and
>only go back to the heap for entries that appear to pass the join test.
>
>
>

We tried that scenario, writing a "dirty index hack" to experiment
with, that returned values whether they were valid or not. We saw some
definite improvements inside of joins and sub queries, but we were still
slowed down at the end because we still had to validate every row being
returned.

My hands are very tied as to what specific examples I can send, so I
apologize for how long it took to get back to you on this. A simple
(generalized) example of one the types of queries we are running:

SELECT col1, col2, cool_func(stat_count,
COALESCE(raw_counts.raw_count, (SELECT alt_count FROM alt_raw_table
WHERE alt_raw_table.pk = col2))) as cool_func

FROM
(SELECT col1, col2, stat_count FROM pair_table WHERE col1 = $1)
pair_table
LEFT JOIN
raw_counts
ON pair_table.col2 = raw_counts.pk

We tried not validating the return of both of these as we only want to
see the rows which have a high value for cool_func, but it was still
necessary to validate the rows which did match the criteria. So we did
see an improvement, but not enough.