From: Ian Burrell <ianburrell@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Nested loop join and date range query
Date: Wed, 3 May 2006 10:54:04 -0700
Message-ID: <d91f09cd0605031054s59984e3doa7ad3222f14b1f15@mail.gmail.com> (raw)
In-Reply-To: <9688.1146625437@sss.pgh.pa.us>
References: <d91f09cd0605021555t42905570s5df4dc005190733b@mail.gmail.com>
<9688.1146625437@sss.pgh.pa.us>
On 5/2/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "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.
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ianburrell@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Nested loop join and date range query
In-Reply-To: <d91f09cd0605031054s59984e3doa7ad3222f14b1f15@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox