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..

2009/11/20 Віталій Тимчишин <tivv00(at)gmail(dot)com>

>
>
> 20 листопада 2009 р. 17:01 Jonathan Foy <thefoy(at)gmail(dot)com> написав:
>
> This seems to result in the same problem; should I attempt to pull for a
>> specific id_nbr/id_qfr, postgres uses the index without a problem. If I try
>> to join the two tables/views however, it insists on doing a sequential scan
>> (actually two in this case) and will not use the index. Any other
>> ideas/explanations?
>>
>
> Have you tried to do same (join) when not using the viewes or converting
> columns into records? May be the problem is not in conversion, but in
> something simplier, like statistics or index bloat?
>
> Best regards, Vitalii Tymchyshyn
>