| From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
|---|---|
| To: | Mark Davidson <mark(at)4each(dot)co(dot)uk> |
| Cc: | Kevin Grittner <kgrittn(at)ymail(dot)com>, Greg Williamson <gwilliamson39(at)yahoo(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: INDEX Performance Issue |
| Date: | 2013-04-15 19:37:33 |
| Message-ID: | CAMkU=1w6=y6hj9aWrSmc0g1hh7KnpgJZWgj=pTWEcgQWa8NAQg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Sun, Apr 7, 2013 at 3:22 PM, Mark Davidson <mark(at)4each(dot)co(dot)uk> wrote:
> Takes a little longer with the INNER join unfortunately. Takes about ~3.5
> minutes, here is the query plan http://explain.depesz.com/s/EgBl.
>
> With the JOIN there might not be a match if the data does not fall within
> one of the areas that is selected in the IN query.
>
> So if we have data id (10) that might fall in areas ( 1, 5, 8, 167 ) but
> the user might be querying areas ( 200 ... 500 ) so no match in area would
> be found just to be absolutely clear.
>
I'm not clear on what you *want* to happen. Are you sure it works the way
you want it to now? If you want every specified id to return at least one
row even if there is no qualified area matching it, you have to move the
LEFT JOIN one join to the left, and have to move the IN list criteria from
the WHERE to the JOIN.
Cheers,
Jeff
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jeff Janes | 2013-04-15 20:16:17 | Re: INDEX Performance Issue |
| Previous Message | Jeff Janes | 2013-04-15 19:30:51 | Re: INDEX Performance Issue |