To: "Ian Burrell" <ianburrell@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Nested loop join and date range query 
In-reply-to: <d91f09cd0605021555t42905570s5df4dc005190733b@mail.gmail.com> 
References: <d91f09cd0605021555t42905570s5df4dc005190733b@mail.gmail.com>
Comments: In-reply-to "Ian Burrell" <ianburrell@gmail.com>
	message dated "Tue, 02 May 2006 15:55:57 -0700"
Date: Tue, 02 May 2006 23:03:57 -0400
Message-ID: <9688.1146625437@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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