Re: Nested loop join and date range query

"Ian Burrell" <ianburrell(at)gmail(dot)com> 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