DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
	h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=lW/RcUs5eKNo7Ut7M1EpOetS5GUIFDivl87OkX4IbUqA687JaZjbX9WJ/70Rw9RhSxESzP981ZWaC5OdfqN/3hTvKD+NtqzmJIqESYpliUkkGBQrq6elSVmTyq2LF0zWu94jeDxXxbjD+z0nNlDz+WixoHNhvhbG8BND7oqIa5k=;
References: 
 <CAPpwVW1rja3ZSsAt1myg-Ptue6d_HRuSXBwMOmuBe_t4HyPgRg@mail.gmail.com>
Message-ID: <1365179846.6562.YahooMailNeo@web162903.mail.bf1.yahoo.com>
Date: Fri, 5 Apr 2013 09:37:26 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: INDEX Performance Issue
To: Mark Davidson <mark@4each.co.uk>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAPpwVW1rja3ZSsAt1myg-Ptue6d_HRuSXBwMOmuBe_t4HyPgRg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Mark Davidson <mark@4each.co.uk> wrote:

>=A0=A0 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.=A0 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?=A0 (They don't.)

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


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance