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.