On 5/2/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Ian Burrell" <[EMAIL PROTECTED]> writes: > We recently upgraded to PostgreSQL 8.1 from 7.4 and a few queries are > having performance problems and running for very long times. The > commonality seems to be PostgreSQL 8.1 is choosing to use a nested > loop join because it estimates there will be only be a single row.We've already noted that there's a problem with estimating zero-width ranges (too lazy to search the archives, but this has come up at least twice recently). Can you modify your app to generate something like week >= x and week < x+1 instead of week >= x and week <= x
I am working on modifying the SQL generation code to replace the zero-width range with an equals. Does BETWEEN have the same bug?
? My recollection is that the fix will probably be complicated enough to not get back-patched into 8.1. BTW, AFAIK the same problem exists in 7.4. What kind of estimates/plans were you getting for this case in 7.4?
We get similar rows=1 estimates on 7.4. 7.4 doesn't choose to use the nested loop joins so it performs fine. We have been getting similar rows=1 estimates and nested loop joins with some other queries. But I think those are caused by not frequently analyzing log type tables and then searching for recent days which it doesn't think exist. - Ian ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
