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.
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.
On 7 April 2013 16:15, Kevin Grittner <kgrittn@xxxxxxxxx> wrote:
Greg Williamson <gwilliamson39@xxxxxxxxx> wrote:Yeah, that is what I was suggesting.
>> Thanks for your response. I tried doing what you suggested so
>> that table now has a primary key of
>> ' CONSTRAINT data_area_pkey PRIMARY KEY(area_id , data_id ); '
>> and I've added the INDEX of
>> 'CREATE INDEX data_area_data_id_index ON data_area USING btree (data_id );'
>> unfortunately it hasn't resulted in an improvement of the query
>> performance.
> Did you run analyze on the table after creating the index ?That probably isn't necessary. Statistics are normally on relations
and columns; there are only certain special cases where an ANALYZE
is needed after an index build, like if the index is on an
_expression_ rather than a list of columns.
Mark, what happens if you change that left join to a normal (inner)
join? Since you're doing an inner join to data_area and that has a
foreign key to area, there should always be a match anyway, right?
The optimizer doesn't recognize that, so it can't start from the
area and just match to the appropriate points.
