"Ian Burrell" <ianburrell@xxxxxxxxx> 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. > -> Index Scan using unq_xtns_by_mso_title_wk on > xtns_by_mso_title_wk x (cost=0.00..4.12 rows=1 width=26) (actual > time=7.827..1297.681 rows=3934 loops=1) > Index Cond: ((week >= '2006-04-23 > 00:00:00'::timestamp without time zone) AND (week <= '2006-04-23 > 00:00:00'::timestamp without time zone) AND (mso_no = 50)) 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 ? 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? regards, tom lane
