From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig James <cjames@emolecules.com>
cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: 100x slowdown for nearly identical tables
In-reply-to: 
 <CAFwQ8rchn1J9Jic=FaHDsHG2cTT+s_pFr2nuNy7PTG3_LCEYnA@mail.gmail.com>
References: 
 <CAFwQ8rfY0X_uMiAczWz4tp+E=mFQC0ohMt-F1MrETMDKVBXqBw@mail.gmail.com>
	<7768.1367453912@sss.pgh.pa.us>
	<CAFwQ8rchn1J9Jic=FaHDsHG2cTT+s_pFr2nuNy7PTG3_LCEYnA@mail.gmail.com>
Comments: In-reply-to Craig James <cjames@emolecules.com>
	message dated "Wed, 01 May 2013 17:45:11 -0700"
Date: Wed, 01 May 2013 21:51:16 -0400
Message-ID: <9392.1367459476@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Craig James <cjames@emolecules.com> writes:
> On Wed, May 1, 2013 at 5:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It looks like old_str_conntab is more or less clustered by "id",
>> and str_conntab not so much.  You could try EXPLAIN (ANALYZE, BUFFERS)
>> (on newer PG versions) to verify how many distinct pages are getting
>> touched during the indexscan.

> Yeah, now that you say it, it's obvious.  The original table was built with
> ID from a sequence, so it's going to be naturally clustered by ID.  The new
> table was built by reloading the data in alphabetical order by supplier
> name, so it would have scattered the IDs all over the place.

> I guess I could actually cluster the new table, but since that one table
> holds about 90% of the total data in the database, that would be a chore.
> Probably better to find a more efficient way to do the query.

Just out of curiosity, you could try forcing a bitmap indexscan to see
how much that helps.  The planner evidently thinks "not at all", but
it's been wrong before ;-)

			regards, tom lane


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