Re: Slow query after upgrade from 8.2 to 8.4

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Slow query after upgrade from 8.2 to 8.4
Дата
Msg-id 27472.1323444617@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Slow query after upgrade from 8.2 to 8.4  (Kaloyan Iliev Iliev <kaloyan@digsys.bg>)
Ответы Re: Slow query after upgrade from 8.2 to 8.4  (Mark Kirkwood <mark.kirkwood@catalyst.net.nz>)
Список pgsql-performance
Kaloyan Iliev Iliev <kaloyan@digsys.bg> writes:
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
>   <meta content="text/html;charset=windows-1251"
>  http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Hi,<br>
> Actually I think the problem is with this sub query:<br>
> explain analyze select 1<br>
>                                        from acc_clients AC,<br>
>                                             acc_debts AD,<br>
>                                             debts_desc DD,<br>
>                                             config CF<br>
>                                        where AC.ino = 1200000 AND<br>
> <br>
>                                              CF.id = (select id<br>
>                                                        from config<br>
>                                                        where
> confid=CF.confid ORDER BY archived_at DESC LIMIT 1) AND<br>
>                                              AD.transact_no =
> AC.transact_no AND<br>
>                                              AD.debtid = DD.debtid AND<br>
>                                               CF.office = 18 AND<br>
>                                              DD.refid = CF.confid LIMIT
> 1;</tt><br>
> <br>
> Instead of starting from '<tt>AC.ino = 1200000' and  limit the rows IT
> start with '</tt><tt>CF.office = 18' which returns much more rows:<br>

Please don't post HTML mail.

I think the real issue is that you've got an astonishingly expensive
approach to keeping obsolete "config" rows around.  You should get rid
of that "ORDER BY archived_at" sub-select, either by not storing
obsolete rows at all (you could move them to a history table instead),
or by marking valid rows with a boolean flag.

However, it's not apparent to me why you would see any difference
between 8.2 and 8.4 on this type of query.  I tried a query analogous
to this one on both, and got identical plans.  I'm guessing that your
slowdown is due to not having updated statistics on the new
installation, or perhaps failing to duplicate some relevant settings.

            regards, tom lane

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

Предыдущее
От: Kaloyan Iliev Iliev
Дата:
Сообщение: Re: Slow query after upgrade from 8.2 to 8.4
Следующее
От: Daniel Cristian Cruz
Дата:
Сообщение: Common slow query reasons - help with a special log