From: | "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com> |
---|---|
To: | andrew(at)pillette(dot)com |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: How to optimize a JOIN with BETWEEN? |
Date: | 2006-02-22 00:14:56 |
Message-ID: | 20060222001456.GZ77800@pervasive.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Use a gist index. Easiest way would be to define a box with mindate at
one corner and maxdate at the other corner, and then search for
point(obsdate,obsdate) that lie with in the box.
A more detailed explination is in the archives somewhere...
On Sun, Feb 19, 2006 at 08:06:12PM -0800, andrew(at)pillette(dot)com wrote:
> Here's a simplified version of the schema:
>
> Table A has an ID field, an observation date, and other stuff. There are about 20K IDs and 3K observations per ID. Table B has a matching ID field, minimum and maximum dates, a code, and other stuff, about 0-50 records per ID. For a given ID, the dates in B never overlap. On A, the PK is (id, obsdate). On B, the PK is (id, mindate). I want
>
> SELECT a.id, b.code, AVG(other stuff) FROM A LEFT JOIN B ON a.id=b.id AND a.obsdate BETWEEN b.mindate AND b.maxdate GROUP BY 1,2;
>
> Is there a way to smarten the query to take advantage of the fact at most one record of B matches A? Also, I have a choice between using a LEFT JOIN or inserting dummy records into B to fill in the gaps in the covered dates, which would make exactly one matching record. Would this make a difference?
>
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2006-02-22 09:38:04 | Re: Help with nested loop left join performance |
Previous Message | Ibrahim Tekin | 2006-02-21 20:28:09 | Re: LIKE query on indexes |