Mark Davidson <mark@4each.co.uk> wrote:
> CONSTRAINT data_area_pkey PRIMARY KEY (data_id , area_id ),
So the only index on this 250 million row table starts with the ID
of the point, but you are joining to it by the ID of the area.
That's requires a sequential scan of all 250 million rows. Switch
the order of the columns in the primary key, add a unique index
with the columns switched, or add an index on just the area ID.
Perhaps you thought that the foreign key constraints would create
indexes? (They don't.)
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company