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@xxxxxxxxx>
20 листопада 2009 р. 17:01 Jonathan Foy <thefoy@xxxxxxxxx> написав: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
