Re: Postgres 8.3, four times slower queries?

Список
Период
Сортировка
От Tom Lane
Тема Re: Postgres 8.3, four times slower queries?
Дата
Msg-id 2870.1236050632@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Postgres 8.3, four times slower queries?  (Aaron Guyon)
Ответы Re: Postgres 8.3, four times slower queries?  (Aaron Guyon)
Список pgsql-performance
Дерево обсуждения
Postgres 8.3, four times slower queries?  (Aaron Guyon, )
 Re: Postgres 8.3, four times slower queries?  (Tom Lane, )
  Re: Postgres 8.3, four times slower queries?  (Aaron Guyon, )
   Re: Postgres 8.3, four times slower queries?  (, )
    Re: Postgres 8.3, four times slower queries?  (Aaron Guyon, )
   Re: Postgres 8.3, four times slower queries?  (Tom Lane, )
    Re: Postgres 8.3, four times slower queries?  (Aaron Guyon, )
     Re: Postgres 8.3, four times slower queries?  (Aaron Guyon, )
     Re: Postgres 8.3, four times slower queries?  (Aaron Guyon, )
      Re: Postgres 8.3, four times slower queries?  (Aaron Guyon, )
       Re: Postgres 8.3, four times slower queries?  ("Kevin Grittner", )
        Re: Postgres 8.3, four times slower queries?  (Robert Haas, )
     Re: Postgres 8.3, four times slower queries?  (Robert Haas, )
 Re: Postgres 8.3, four times slower queries?  (Robert Haas, )
 Re: Postgres 8.3, four times slower queries?  (Aaron Guyon, )
 Re: Postgres 8.3, four times slower queries?  (Aaron Guyon, )
Aaron Guyon <> writes:
> We are currently running postgres 8.2 and are evaluating the upgrade to 8.3.
> Some of our tests are indicating that postgresql 8.3 is actually degrading
> the performance of some of our queries by a factor of 10 or more.

Are you sure you are comparing apples to apples here?  Same configure
options for the builds, same parameter values in postgresql.conf, both
databases ANALYZEd, etc?  And are they running on the same hardware?

The rowcount estimates seem to be a bit different, which might account
for the difference in plan choices, but I'm not convinced that that is
the reason for the slowness.  The parts of the plans that are exactly
comparable show very significant speed differences, eg

>              ->  Index Scan using idx_skin_day_part_id on skin t2  (cost=0.00..6.28 rows=1 width=24) (actual
time=2.484..2.486rows=1 loops=7) 
>                    Index Cond: (t2.day_part_id = t10.id)
>                    Filter: (t2.active <> 0::numeric)
>        ->  Index Scan using idx_skin_slot_skin_id on skin_slot t11  (cost=0.00..6.54 rows=92 width=25) (actual
time=12.726..276.412rows=94 loops=4) 
>              Index Cond: (t11.skin_id = t2.id)
>              Filter: (t11.active <> 0::numeric)

>              ->  Index Scan using idx_skin_day_part_id on skin t2  (cost=0.00..6.28 rows=1 width=30) (actual
time=0.028..0.031rows=1 loops=7) 
>                    Index Cond: (t2.day_part_id = t10.id)
>                    Filter: (active <> 0::numeric)
>        ->  Index Scan using idx_skin_slot_skin_id on skin_slot t11  (cost=0.00..6.85 rows=93 width=30) (actual
time=0.053..1.382rows=94 loops=4) 
>              Index Cond: (t2.id = t11.skin_id)
>              Filter: (active <> 0::numeric)

There's nothing in 8.3 vs 8.2 to explain that, if they're configured
the same and running in the same environment.

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Postgres 8.3, four times slower queries?
Следующее
От: Hans Liebenberg
Дата:
Сообщение: Substring search using "exists" with a space in the search term