Re: 100x slowdown for nearly identical tables

От: Tom Lane
Тема: Re: 100x slowdown for nearly identical tables
Дата: ,
Msg-id: 9392.1367459476@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: 100x slowdown for nearly identical tables  (Craig James)
Список: pgsql-performance

Скрыть дерево обсуждения

100x slowdown for nearly identical tables  (Craig James, )
 Re: 100x slowdown for nearly identical tables  (Tom Lane, )
  Re: 100x slowdown for nearly identical tables  (Craig James, )
   Re: 100x slowdown for nearly identical tables  (Tom Lane, )

Craig James <> writes:
> On Wed, May 1, 2013 at 5:18 PM, Tom Lane <> 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



В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: 100x slowdown for nearly identical tables
От: Simon Riggs
Дата:
Сообщение: Re: In progress INSERT wrecks plans on table