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:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=n7JyXvG9oXpvngusp63gYb55+ixEht9/8sDsxYk2GvgF04N5gra3aGlYIFjf1A7T0GEUzPb+ZqbMvHlWWN61MwXR83eEBaVzTGUNKgbW4yqM6+nlqEPHwSgozp7/qbxikIht7h1FlWHmGF9ANGcZ2Kl6MVMxknzLSvho0OOxpVw=;
References: 
 <CAPpwVW1rja3ZSsAt1myg-Ptue6d_HRuSXBwMOmuBe_t4HyPgRg@mail.gmail.com>
	<1365179846.6562.YahooMailNeo@web162903.mail.bf1.yahoo.com>
	<CAPpwVW2ecnmDeqL6CTNCzDe0GwQfkCoZEKQy1rOuoR6FUSTshA@mail.gmail.com>
	<1365330080.67650.YahooMailNeo@web125903.mail.ne1.yahoo.com>
Message-ID: <1365347742.50505.YahooMailNeo@web162901.mail.bf1.yahoo.com>
Date: Sun, 7 Apr 2013 08:15:42 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: INDEX Performance Issue
To: Greg Williamson <gwilliamson39@yahoo.com>,
 Mark Davidson <mark@4each.co.uk>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <1365330080.67650.YahooMailNeo@web125903.mail.ne1.yahoo.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

Greg Williamson <gwilliamson39@yahoo.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.=A0 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?=A0 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.

--=20
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