From: Kevin Grittner <kgrittn@ymail.com>
To: Greg Williamson <gwilliamson39@yahoo.com>
To: Mark Davidson <mark@4each.co.uk>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: INDEX Performance Issue
Date: Sun, 7 Apr 2013 08:15:42 -0700 (PDT)
Message-ID: <1365347742.50505.YahooMailNeo@web162901.mail.bf1.yahoo.com> (raw)
In-Reply-To: <1365330080.67650.YahooMailNeo@web125903.mail.ne1.yahoo.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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. 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kgrittn@ymail.com, gwilliamson39@yahoo.com, mark@4each.co.uk
Subject: Re: INDEX Performance Issue
In-Reply-To: <1365347742.50505.YahooMailNeo@web162901.mail.bf1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox