Craig James <cjames@xxxxxxxxxxxxxx> writes: > On Wed, May 1, 2013 at 5:18 PM, Tom Lane <tgl@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
