Re: INDEX Performance Issue

Greg Williamson <gwilliamson39(at)yahoo(dot)com> wrote:

>> 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 );'

Yeah, that is what I was suggesting.

>> 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.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company