Re: Nested loop join and date range query

"Ian Burrell" <ianburrell@gmail.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