Re: 100x slowdown for nearly identical tables

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