Re: View based upon function won't use index on joins

I don't think so. I actually dumped the tables involved into stripped down versions of themselves in a new database for testing, so the data involved should be completely fresh.  I ran a vacuum analyze after the dump of course.

Just for paranoia's sake though I did do the following:

explain analyze select id_nbr, id_qfr,
val_1_cd_1,
        val_1_cd_2,
        ...
        val_2_amt_12
from value_codes
where main_table.create_dt >= '20091001'
and main_table.id_nbr = value_codes.id_nbr
and main_table.id_qfr = value_codes.id_qfr

with the following results

"Nested Loop  (cost=0.00..1592.17 rows=132 width=150) (actual time=0.093..1.075 rows=4 loops=1)"
"  ->  Index Scan using main_table_create_dt_index on main_table  (cost=0.00..21.47 rows=194 width=6) (actual time=0.035..0.249 rows=53 loops=1)"
"        Index Cond: (create_dt >= '20091001'::bpchar)"
"  ->  Index Scan using value_codes_pkey on value_codes  (cost=0.00..8.08 rows=1 width=150) (actual time=0.007..0.007 rows=0 loops=53)"
"        Index Cond: ((value_codes.id_nbr = main_table.id_nbr) AND (value_codes.id_qfr = main_table.id_qfr))"
"Total runtime: 1.279 ms"


I'm stumped.  I'm starting to think that I'm trying to get postgres to do something that it just doesn't do.  Shy of just throwing a trigger in the table to actually populate a second table with the same data solely for reporting purposes, which I hate to do for obvious reasons, I don't know what else to do.  And this is only one example of this situation in the databases that I'm dealing with, I was hoping to come up with a more generic solution that I could apply in any number of locations.

I do very much appreciate the responses...I've been gradually getting deeper and deeper into postgres, and am still very much learning as I go.  All advice is very helpful.

Thanks..