Re: 100x slowdown for nearly identical tables

Craig James <cjames(at)emolecules(dot)com> writes:
> On Wed, May 1, 2013 at 5:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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