From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ian Burrell <ianburrell@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Nested loop join and date range query
Date: Tue, 02 May 2006 23:03:57 -0400
Message-ID: <9688.1146625437@sss.pgh.pa.us> (raw)
In-Reply-To: <d91f09cd0605021555t42905570s5df4dc005190733b@mail.gmail.com>
References: <d91f09cd0605021555t42905570s5df4dc005190733b@mail.gmail.com>
"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
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: tgl@sss.pgh.pa.us, ianburrell@gmail.com
Subject: Re: Nested loop join and date range query
In-Reply-To: <9688.1146625437@sss.pgh.pa.us>
* 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