Re: 100x slowdown for nearly identical tables

От: Tom Lane
Тема: Re: 100x slowdown for nearly identical tables
Дата: ,
Msg-id: 7768.1367453912@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: 100x slowdown for nearly identical tables  (Craig James)
Ответы: 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:
> I have two tables that are nearly identical, yet the same query runs 100x
> slower on the newer one. ...

> db=> explain analyze select id, 1 from str_conntab
> where (id >= 12009977 and id <= 12509976) order by id;

>  Index Scan using new_str_conntab_pkey_3217 on str_conntab
>       (cost=0.00..230431.33 rows=87827 width=4)
>       (actual time=65.771..51341.899 rows=48613 loops=1)
>    Index Cond: ((id >= 12009977) AND (id <= 12509976))
>  Total runtime: 51350.556 ms

> db=> explain analyze select id, 1 from old_str_conntab
> where (id >= 12009977 and id <= 12509976) order by id;

>  Index Scan using str_conntab_pkey on old_str_conntab
>      (cost=0.00..82262.56 rows=78505 width=4)
>      (actual time=38.327..581.235 rows=48725 loops=1)
>    Index Cond: ((id >= 12009977) AND (id <= 12509976))
>  Total runtime: 586.071 ms

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.

            regards, tom lane



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

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