Re: problem with from_collapse_limit and joined views

От: Kevin Grittner
Тема: Re: problem with from_collapse_limit and joined views
Дата: ,
Msg-id: 4CFA09E2020000250003829C@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответы: Re: problem with from_collapse_limit and joined views  (Tom Lane)
Re: problem with from_collapse_limit and joined views  (Markus Schulz)
Список: pgsql-performance

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

Re: problem with from_collapse_limit and joined views  ("Kevin Grittner", )
 Re: problem with from_collapse_limit and joined views  (Tom Lane, )
  Re: problem with from_collapse_limit and joined views  (Markus Schulz, )
 Re: problem with from_collapse_limit and joined views  (Markus Schulz, )

Markus Schulz  11/24/10 1:02 PM >>>

> if i set "from_collapse_limit" (to merge the views) and
> join_collapse_limit (to explode the explicit joins) high enough
> (approx 32), all is fine (good performance). But other queries are
> really slow in our environment (therefore it's no option to raise
> the join_collapse_limit to a higher value)
>
> With defaults (8) for both, the performance is ugly

One option would be to create a different user for running queries
which read from complex views such as this.

postgres=# create user bob;
CREATE ROLE
postgres=# alter user bob set from_collapse_limit = 40;
ALTER ROLE
postgres=# alter user bob set join_collapse_limit = 40;
ALTER ROLE

Log in as bob, and your queries should run fine.

Nothing leapt out at me as an issue in your postgresql.conf except:

max_prepared_transactions = 20

Do you actually use prepared transactions?  (Sometimes people confuse
this with prepared statements, which are a completely different
feature.)

-Kevin


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

От: felix
Дата:
Сообщение: Re: Update problem on large table
От: John Papandriopoulos
Дата:
Сообщение: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT