Also, i tried to see the matching id for squarefeet in attribute table there are 800 some records in attribute table for 8 version and 700 something in 7.4 version.
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
On 8
common | attribute | fknamestringid | 0 | 4 | 80 | {2524,2434,2530,2522,2525,2523,2527,2526,2574,2531} | {0.219333,0.199333,0.076,0.0643333,0.0616667,0.05,0.0453333,0.042,0.04,0.0286667} | {2437,2528,2529,2538,2539,2540,2554,2562,2575,2584,2637} | 0.0274016
Given those stats, the planner is going to estimate that about 1/80th of the attribute table matches any particular fknamestringid, and that's what's driving it away from using the indexscan. I cannot tell whether there are indeed a couple of thousand rows joining to the 'squareFeet' string row (in which case the condition numericValue='775.0' must be really selective) or whether this is an outlier case that joins to just a few attribute rows.
The slightly different stats values for 7.4 would have given it a slightly lower value for the cost of an indexscan by idx_attribute_fknamestringid, but certainly not as low as your original message shows. Perhaps you have some difference in parameter settings in your 7.4 installation --- most likely a lower random_page_cost.
regards, tom lane
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
