Re: [GENERAL] Slow query plan used

Поиск
Список
Период
Сортировка
От Andreas Kretschmer
Тема Re: [GENERAL] Slow query plan used
Дата
Msg-id 64d2c8f8-1bf1-21f1-563f-e2b0d414fcfe@a-kretschmer.de
обсуждение исходный текст
Ответ на [GENERAL] Slow query plan used  ("Wetzel, Juergen (Juergen)" <wetzel@avaya.com>)
Ответы Re: [GENERAL] Slow query plan used  ("Wetzel, Juergen (Juergen)" <wetzel@avaya.com>)
Список pgsql-general


Am 30.05.2017 um 10:42 schrieb Wetzel, Juergen (Juergen):

> I have a question concerning the query planner. I observe that chosen query plan differs on length and content of a like
> search expression. We have a view combining data from two tables, both containing same number of rows (round about 30000).
> Used PostgreSQL version is 9.3.15 on Windows. DDL of tables and view see below.


the query is on DOCUMENTDATA, but plan and DDL for c3k_documentdata and other tables.

> Only 130 rows out of the 30000 have ARCHIVED = 0

in this case i would suggest a partial index:

create index <indexname> on <tablename> (archived) where archived = 0;

You can also increase the statistics for the columns subject and 
mailContent


ALTER TABLE <tablename> ALTER COLUMN <column_name> SET STATISTICS = 1000;

(as example, the default-value is 100)


Regards, Andreas
-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

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

Предыдущее
От: Alban Hertroys
Дата:
Сообщение: Re: 9.5 "chained equality" behavior
Следующее
От: "Wetzel, Juergen (Juergen)"
Дата:
Сообщение: Re: [GENERAL] Slow query plan used