Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса / 25 сообщений из 45, страница 1 из 2
  1  все
10.02.2016, 11:21
    #39167374
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Добрый день. Можно ли еще дооптимизировать такой скрипт:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SELECT _c.cause_id,_c.number as ncase,string_agg(_pt.participant_type || ': ' || _p.participant, ',
     ') as part_case, _d.judge , _d.judges, _c.in_date as date,_c.is_active
            FROM document _d
            JOIN cause _c ON _d.cause_id = _c.cause_id and _d.cause_db_id = _c.cause_db_id
            left join participant _p ON _p.cause_id = _c.cause_id and _p.cause_db_id = _c.cause_db_id
            left join participant_type _pt on _pt.participant_type_id = _p.participant_type_id and _pt.participant_type_db_id = _p.participant_type_db_id

     WHERE _c.cause_db_id = '2605'  AND _pt.participant_type !~~* '%свідок%'
                AND _c.in_date BETWEEN to_date('01.03.2015','dd.mm.yyyy') AND to_date('31.03.2015','dd.mm.yyyy')
				--AND _c.in_date BETWEEN '2015-03-01' AND '2015-03-31'
                --and _c.in_date is not NULL
                AND ((_d.doctype_id = 213207 AND _d.doctype_db_id = 0)  OR (_d.doctype_id = 403760 AND _d.doctype_db_id = 0))
                AND coalesce(_c.include_stat,'1') = 1
                AND _c.deleted <> 1 
                AND (_c.number ILIKE '%Миронович%' 
                OR _p.participant ILIKE '%Миронович%' 
                OR _d.judge ILIKE '%Миронович%' 
                OR _d.judges ILIKE '%Миронович%')
    group by _c.cause_id,_c.number,_d.judge, _d.judges,_c.in_date,_c.is_active
    order by _c.cause_id


План запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
QUERY PLAN
GroupAggregate  (cost=5714.53..5714.57 rows=1 width=313)
  ->  Sort  (cost=5714.53..5714.53 rows=1 width=313)
        Sort Key: _c.cause_id, _c.number, _d.judge, _d.judges, _c.in_date, _c.is_active
        ->  Nested Loop  (cost=0.01..5714.52 rows=1 width=313)
              ->  Nested Loop  (cost=0.01..5709.57 rows=1 width=269)
                    Join Filter: ((_c.cause_id = _p.cause_id) AND (((_c.number)::text ~~* '%Миронович%'::text) OR ((_p.participant)::text ~~* '%Миронович%'::text) OR ((_d.judge)::text ~~* '%Миронович%'::text) OR ((_d.judges)::text ~~* '%Миронович%'::text)))
                    ->  Nested Loop  (cost=0.01..5553.92 rows=1 width=161)
                          ->  Index Scan using cause_idx_cause_db_id_in_date on cause _c  (cost=0.01..4998.10 rows=6 width=33)
                                Index Cond: ((cause_db_id = 2605) AND (in_date >= to_date('01.03.2015'::text, 'dd.mm.yyyy'::text)) AND (in_date <= to_date('31.03.2015'::text, 'dd.mm.yyyy'::text)))
                                Filter: ((deleted <> 1) AND (COALESCE(include_stat, 1::smallint) = 1))
                          ->  Index Scan using idx_document_cause on document _d  (cost=0.00..92.63 rows=1 width=132)
                                Index Cond: ((cause_db_id = 2605) AND (cause_id = _c.cause_id))
                                Filter: ((doctype_db_id = 0) AND ((doctype_id = 213207) OR (doctype_id = 403760)))
                    ->  Index Scan using participant_idx_cause_id on participant _p  (cost=0.00..155.63 rows=1 width=124)
                          Index Cond: ((cause_id = _d.cause_id) AND (cause_db_id = 2605))
              ->  Index Scan using idx_participant_type__participant_type_id on participant_type _pt  (cost=0.00..4.94 rows=1 width=60)
                    Index Cond: (participant_type_id = _p.participant_type_id)
                    Filter: (((participant_type)::text !~~* '%свідок%'::text) AND (_p.participant_type_db_id = participant_type_db_id))


самый большой "тормоз" на условие по дате, может есть способ как это обойти?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 11:52
    #39169400
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Неужели нет идей? может необходима дополнительная информация?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 12:09
    #39169427
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Sheriffua,

explain analyze запроса приведите.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 12:52
    #39169489
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
SheriffuaНеужели нет идей? может необходима дополнительная информация?
основная идея идея -- кг/ам

если охолонуть -- то сэкономить по мелочи на ширине ключа сортировки, или даже загнать на hash aggreagate (вместо group-//-)

куда--то сюда
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
SELECT
	_c.cause_id
	,_c.number as ncase
	,_d.part_case
	,_d.judge
	,_d.judges
	,_c.in_date as date
	,_c.is_active
FROM cause _c
,LATERAL 
	(SELECT 
		string_agg(_pt.participant_type || ': ' || _p.participant, ',
		') as part_case
		, _d.judge
		, _d.judges

	FROM document _d
		left join participant _p ON _p.cause_id = _c.cause_id and _p.cause_db_id = _c.cause_db_id
		left join participant_type _pt on _pt.participant_type_id = _p.participant_type_id and _pt.participant_type_db_id = _p.participant_type_db_id
	WHERE
		_d.cause_id = _c.cause_id and _d.cause_db_id = _c.cause_db_id
		AND _pt.participant_type !~~* '%свідок%'

		AND (_c.number ILIKE '%Миронович%' 
			OR _d.participant ILIKE '%Миронович%' 
			OR _d.judge ILIKE '%Миронович%' 
			OR _d.judges ILIKE '%Миронович%'
		)
	group by _d.judge, _d.judges
) _d

WHERE	_c.cause_db_id = 2605
		AND _c.in_date BETWEEN DATE'2015-03-01' AND  DATE'2015-03-31'
		--AND coalesce(_c.include_stat,'1') = 1 -- УБИВАТЬ ПИДАРАСОВ
			-- тут ещё и неявное приведение типов, ять
		AND (_c.include_stat=1 OR _c.include_stat IS NULL) -- какой тип , ять ?
		--AND (_c.include_stat='1' OR _c.include_stat IS NULL)
		AND _c.deleted <> 1 

order by _c.cause_id

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 16:31
    #39169710
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Alexius,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
QUERY PLAN
GroupAggregate  (cost=4342.88..4342.92 rows=1 width=313) (actual time=21907.043..21907.048 rows=2 loops=1)
  ->  Sort  (cost=4342.88..4342.89 rows=1 width=313) (actual time=21907.019..21907.020 rows=2 loops=1)
        Sort Key: _c.cause_id, _c.number, _d.judge, _d.judges, _c.in_date, _c.is_active
        Sort Method: quicksort  Memory: 17kB
        ->  Nested Loop  (cost=0.01..4342.87 rows=1 width=313) (actual time=108.669..21906.963 rows=2 loops=1)
              ->  Nested Loop  (cost=0.01..4337.92 rows=1 width=269) (actual time=108.638..21906.899 rows=2 loops=1)
                    Join Filter: ((_c.cause_id = _p.cause_id) AND (((_c.number)::text ~~* '%Миронович%'::text) OR ((_p.participant)::text ~~* '%Миронович%'::text) OR ((_d.judge)::text ~~* '%Миронович%'::text) OR ((_d.judges)::text ~~* '%Миронович%'::text)))
                    Rows Removed by Join Filter: 2009
                    ->  Nested Loop  (cost=0.01..4182.23 rows=1 width=161) (actual time=94.590..15281.129 rows=1292 loops=1)
                          ->  Index Scan using cause_idx_cause_db_id_in_date on cause _c  (cost=0.01..3718.05 rows=5 width=33) (actual time=14.653..7853.103 rows=1558 loops=1)
                                Index Cond: ((cause_db_id = 2605) AND (in_date >= to_date('01.01.2016'::text, 'dd.mm.yyyy'::text)) AND (in_date <= to_date('31.01.2016'::text, 'dd.mm.yyyy'::text)))
                                Filter: ((deleted <> 1) AND (COALESCE(include_stat, 1::smallint) = 1))
                                Rows Removed by Filter: 4
                          ->  Index Scan using idx_document_cause on document _d  (cost=0.00..92.83 rows=1 width=132) (actual time=4.761..4.762 rows=1 loops=1558)
                                Index Cond: ((cause_db_id = 2605) AND (cause_id = _c.cause_id))
                                Filter: ((doctype_db_id = 0) AND ((doctype_id = 213207) OR (doctype_id = 403760)))
                                Rows Removed by Filter: 1
                    ->  Index Scan using participant_idx_cause_id on participant _p  (cost=0.00..155.67 rows=1 width=124) (actual time=4.024..5.021 rows=2 loops=1292)
                          Index Cond: ((cause_id = _d.cause_id) AND (cause_db_id = 2605))
              ->  Index Scan using idx_participant_type__participant_type_id on participant_type _pt  (cost=0.00..4.94 rows=1 width=60) (actual time=0.023..0.025 rows=1 loops=2)
                    Index Cond: (participant_type_id = _p.participant_type_id)
                    Filter: (((participant_type)::text !~~* '%свідок%'::text) AND (_p.participant_type_db_id = participant_type_db_id))
Total runtime: 21907.195 ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 16:46
    #39169725
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Alexius,

это более правильный план:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
QUERY PLAN
GroupAggregate  (cost=5507.14..5507.18 rows=1 width=313) (actual time=46483.134..46483.134 rows=1 loops=1)
  ->  Sort  (cost=5507.14..5507.15 rows=1 width=313) (actual time=46483.109..46483.109 rows=1 loops=1)
        Sort Key: _c.cause_id, _c.number, _d.judge, _d.judges, _c.in_date, _c.is_active
        Sort Method: quicksort  Memory: 17kB
        ->  Nested Loop  (cost=0.01..5507.13 rows=1 width=313) (actual time=20114.643..46483.088 rows=1 loops=1)
              ->  Nested Loop  (cost=0.01..5502.18 rows=1 width=269) (actual time=20114.601..46483.044 rows=1 loops=1)
                    Join Filter: ((_c.cause_id = _p.cause_id) AND (((_c.number)::text ~~* '%Миронович%'::text) OR ((_p.participant)::text ~~* '%Миронович%'::text) OR ((_d.judge)::text ~~* '%Миронович%'::text) OR ((_d.judges)::text ~~* '%Миронович%'::text)))
                    Rows Removed by Join Filter: 2878
                    ->  Nested Loop  (cost=0.01..5346.49 rows=1 width=161) (actual time=74.265..26551.530 rows=1417 loops=1)
                          ->  Index Scan using cause_idx_cause_db_id_in_date on cause _c  (cost=0.01..4789.47 rows=6 width=33) (actual time=12.448..6250.828 rows=1582 loops=1)
                                Index Cond: ((cause_db_id = 2605) AND (in_date >= to_date('01.03.2015'::text, 'dd.mm.yyyy'::text)) AND (in_date <= to_date('31.03.2015'::text, 'dd.mm.yyyy'::text)))
                                Filter: ((deleted <> 1) AND (COALESCE(include_stat, 1::smallint) = 1))
                          ->  Index Scan using idx_document_cause on document _d  (cost=0.00..92.83 rows=1 width=132) (actual time=7.228..12.827 rows=1 loops=1582)
                                Index Cond: ((cause_db_id = 2605) AND (cause_id = _c.cause_id))
                                Filter: ((doctype_db_id = 0) AND ((doctype_id = 213207) OR (doctype_id = 403760)))
                                Rows Removed by Filter: 2
                    ->  Index Scan using participant_idx_cause_id on participant _p  (cost=0.00..155.67 rows=1 width=124) (actual time=8.382..13.921 rows=2 loops=1417)
                          Index Cond: ((cause_id = _d.cause_id) AND (cause_db_id = 2605))
              ->  Index Scan using idx_participant_type__participant_type_id on participant_type _pt  (cost=0.00..4.94 rows=1 width=60) (actual time=0.035..0.036 rows=1 loops=1)
                    Index Cond: (participant_type_id = _p.participant_type_id)
                    Filter: (((participant_type)::text !~~* '%свідок%'::text) AND (_p.participant_type_db_id = participant_type_db_id))
Total runtime: 46483.281 ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 17:01
    #39169745
ОКТОГЕН
ОКТОГЕН
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Sheriffua,

Скрипт создания таблиц - в студию.
И результат запроса
Код: sql
1.
SELECT version();
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 17:11
    #39169759
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
qwwq,

Вылазить такая ошибка:
Код: sql
1.
2.
3.
ОШИБКА:  ошибка синтаксиса (примерное положение: "SELECT")
LINE 1: ...ate as date ,_c.is_active FROM cause _c, LATERAL (SELECT str...
                                                             ^
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 17:18
    #39169770
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ОКТОГЕН,

 

Код: sql
1.
PostgreSQL 9.2.2, compiled by Visual C++ build 1600, 32-bit


Таблицы:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
CREATE TABLE public.cause (
  cause_id INTEGER NOT NULL,
  cause_db_id INTEGER NOT NULL,
  cause_type_id INTEGER,
  out_date TIMESTAMP WITHOUT TIME ZONE,
  out_number VARCHAR(250) DEFAULT NULL::character varying,
  in_date TIMESTAMP WITHOUT TIME ZONE,
  in_number VARCHAR(250) DEFAULT NULL::character varying,
  number VARCHAR(250) DEFAULT NULL::character varying,
  proceedings VARCHAR(250) DEFAULT NULL::character varying,
  violation_date TIMESTAMP WITHOUT TIME ZONE,
  consideration_date TIMESTAMP WITHOUT TIME ZONE,
  judge VARCHAR(250) DEFAULT NULL::character varying,
  judges VARCHAR(1000),
  closed TIMESTAMP(6) WITHOUT TIME ZONE,
  created TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT now() NOT NULL,
  changed TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
  deleted INTEGER DEFAULT 0 NOT NULL,
  cause_created TIMESTAMP WITHOUT TIME ZONE,
  cause_changed TIMESTAMP WITHOUT TIME ZONE,
  plaintiff VARCHAR(3000),
  respondent VARCHAR(3000),
  flag_r INTEGER,
  flag_p INTEGER,
  is_active INTEGER DEFAULT 0,
  assig_description VARCHAR(1000),
  cause_dep_id INTEGER,
  stage_id INTEGER,
  stage_date TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL::timestamp without time zone,
  cause_result TEXT,
  claim_type VARCHAR(255),
  participiant_inactive INTEGER DEFAULT 0,
  participants VARCHAR,
  include_stat SMALLINT,
  sentence_date DATE,
  CONSTRAINT cause_pkey PRIMARY KEY(cause_id, cause_db_id),
  CONSTRAINT cause_fk_cause_db_id FOREIGN KEY (cause_db_id)
    REFERENCES public.court(court_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    DEFERRABLE
    INITIALLY DEFERRED,
  CONSTRAINT cause_fk_cause_dep_id FOREIGN KEY (cause_dep_id)
    REFERENCES public.court(court_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    DEFERRABLE
    INITIALLY DEFERRED,
  CONSTRAINT cause_fk_cause_type_id FOREIGN KEY (cause_type_id)
    REFERENCES public.cause_type(cause_type_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    DEFERRABLE
    INITIALLY DEFERRED,
  CONSTRAINT cause_fk_stage_id FOREIGN KEY (stage_id)
    REFERENCES public.cause_stages(stage_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    DEFERRABLE
    INITIALLY DEFERRED
) 
WITH (fillfactor = 90, oids = false);

CREATE INDEX cause_idx_cause_db_id_in_date ON public.cause
  USING btree (cause_db_id, in_date)
  WITH (fillfactor = 80);

ALTER TABLE public.cause
  CLUSTER ON cause_idx_cause_db_id_in_date;

CREATE INDEX cause_idx_changed ON public.cause
  USING btree (changed);

CREATE INDEX cause_idx_created ON public.cause
  USING btree (created);

CREATE INDEX cause_idx_in_date ON public.cause
  USING btree (in_date);

CREATE INDEX cause_idx_number ON public.cause
  USING btree (number COLLATE pg_catalog."default" pg_catalog.varchar_pattern_ops);

CREATE INDEX cause_idx_number_left_lower ON public.cause
  USING btree ((lower("left"((number)::text, 20))) COLLATE pg_catalog."POSIX" pg_catalog.varchar_ops);

CREATE INDEX cause_idx_participants ON public.cause
  USING gin ((to_tsvector('simple'::regconfig, (participants)::text)));

CREATE INDEX cause_idx_proceedings ON public.cause
  USING btree (proceedings COLLATE pg_catalog."default" pg_catalog.varchar_pattern_ops);

CREATE INDEX cause_idx_proceedings_left_lower ON public.cause
  USING btree ((lower("left"((proceedings)::text, 20))) COLLATE pg_catalog."POSIX" pg_catalog.varchar_ops);


...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 17:54
    #39169800
ОКТОГЕН
ОКТОГЕН
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Sheriffua,

Скажите вашему серверу
Код: sql
1.
2.
3.
4.
5.
6.
    
CREATE EXTENSION pg_trgm;
CREATE INDEX cause_trgm_idx_number      ON public.cause USING GIN (number gin_trgm_ops);
CREATE INDEX cause_trgm_idx_participant ON public.cause USING GIN (participant gin_trgm_ops);
CREATE INDEX cause_trgm_idx_judge       ON public.cause USING GIN (judge gin_trgm_ops);
CREATE INDEX cause_trgm_idx_judges      ON public.cause USING GIN (judges gin_trgm_ops);


И выдайте EXPLAIN ANALYZE
вашего запроса
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 18:09
    #39169812
ОКТОГЕН
ОКТОГЕН
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ОКТОГЕН,
Опечатался, т.к. вы не дали скрипта на все таблицы
Но идея, думаю понятна.
ИМХО, дата не при чём.
Как создадите индексы по всем полям, где LIKE делаете - напишите какой план получился.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 18:09
    #39169813
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
[quot Sheriffua]
Код: sql
1.
PostgreSQL 9.2.2, compiled by Visual C++ build 1600, 32-bit


тады лейтерал не для вас.

можете поборотся за мелочевку (ширину ключа группировки) группбаем только по _c.id, с последующим повторным чтением остальных полей. Но это скучно.


а триграм по 3-ному межтабличному OR -- довольно смело.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 18:12
    #39169818
ОКТОГЕН
ОКТОГЕН
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
qwwq,
у него всё равно like тормозит.
Вот если ещё на сервере с ресурсами беда, тогда ой...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.02.2016, 21:13
    #39169949
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ОКТОГЕН,qwwq,

С лейтерал понятно - версия не подходит, но не совсем понимаю идею - индексы по всем полям, где LIKE, они есть вроде везде, но об этом буду точно знать в понедельник.


P.S. Спасибо за помощь
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2016, 02:42
    #39170111
ОКТОГЕН
ОКТОГЕН
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
SheriffuaОКТОГЕН,qwwq,

С лейтерал понятно - версия не подходит, но не совсем понимаю идею - индексы по всем полям, где LIKE, они есть вроде везде, но об этом буду точно знать в понедельник.


P.S. Спасибо за помощь
У вас индексы двоичные древья, а LIKE требуются обратные индексы по триграмам.
Смотрите код повнимательнее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2016, 18:17
    #39171670
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ОКТОГЕН,

Целый день убили на игры с этими индексами, НИЧЕГО не поменялось, как был кост ~5248 таким он и остался, такое впечатление, что новые индексы запрос попросту не хочет видеть.

Плюс еще заметил следующие, когда запрос строится без LIKE, кост становится меньшим, но не значительно~5211, так что пока тупик.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2016, 18:30
    #39171686
ОКТОГЕН
ОКТОГЕН
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Sheriffua, а в плане они участвуют? Или вообще никак?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2016, 18:31
    #39171688
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ОКТОГЕН,

вообще НИКАК (если вы про новосозданные индексы). заметил чем больше период даты, то кост увеличивается.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2016, 18:33
    #39171692
ОКТОГЕН
ОКТОГЕН
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Sheriffua, а если прибить все другие на этих полях?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2016, 18:37
    #39171698
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ОКТОГЕН,
вам сразу сказал
автора триграм по 3-ному межтабличному OR -- довольно смело

как вы представляете такую работу по индексам ?



тогда ваш OR надо переписать на UNION [ALL , если повезет] по каждой подвыборке, соотв. каждой ветке из OR.
вот тогда -- оно может сработать по вашим индексам
если область пересечения невелика -- это м.б. ещё и выгодно, но сомневаюсь
но сам пж на это не пустится. это слишком смело, для планера.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2016, 18:45
    #39171707
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
qwwq,

я сейчас обстрагировался от OR, и писал выше, что кост при этом практически не меняется. поэтому видимо "тормоз" нужно искать в другом месте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2016, 19:01
    #39171719
vyegorov
vyegorov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Sheriffua,

А приведите также `EXPLAIN (analyze, buffers)`, чтобы видеть сколько буферов обрабатываются.
И какие у вас измененные настройки:
Код: sql
1.
SELECT name,setting,unit FROM pg_settings WHERE source NOT IN ('default','override');



В помеченой части оптимизатор ошибается и потом бомбит единичными проходами:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
QUERY PLAN
GroupAggregate  (cost=5507.14..5507.18 rows=1 width=313) (actual time=46483.134..46483.134 rows=1 loops=1)
  ->  Sort  (cost=5507.14..5507.15 rows=1 width=313) (actual time=46483.109..46483.109 rows=1 loops=1)
        Sort Key: _c.cause_id, _c.number, _d.judge, _d.judges, _c.in_date, _c.is_active
        Sort Method: quicksort  Memory: 17kB
        ->  Nested Loop  (cost=0.01..5507.13 rows=1 width=313) (actual time=20114.643..46483.088 rows=1 loops=1)
              ->  Nested Loop  (cost=0.01..5502.18 rows=1 width=269) (actual time=20114.601..46483.044 rows=1 loops=1)
                    Join Filter: ((_c.cause_id = _p.cause_id) AND (((_c.number)::text ~~* '%Миронович%'::text) OR ((_p.participant)::text ~~* '%Миронович%'::text) OR ((_d.judge)::text ~~* '%Миронович%'::text) OR ((_d.judges)::text ~~* '%Миронович%'::text)))
                    Rows Removed by Join Filter: 2878
                    ->  Nested Loop  (cost=0.01..5346.49 rows=1 width=161) (actual time=74.265..26551.530 rows=1417 loops=1)
                          ->  Index Scan using cause_idx_cause_db_id_in_date on cause _c  (cost=0.01..4789.47 rows=6 width=33) (actual time=12.448..6250.828 rows=1582 loops=1)
                                Index Cond: ((cause_db_id = 2605) AND (in_date >= to_date('01.03.2015'::text, 'dd.mm.yyyy'::text)) AND (in_date <= to_date('31.03.2015'::text, 'dd.mm.yyyy'::text)))
                                Filter: ((deleted <> 1) AND (COALESCE(include_stat, 1::smallint) = 1))
                          ->  Index Scan using idx_document_cause on document _d  (cost=0.00..92.83 rows=1 width=132) (actual time=7.228..12.827 rows=1 loops=1582)
                                Index Cond: ((cause_db_id = 2605) AND (cause_id = _c.cause_id))
                                Filter: ((doctype_db_id = 0) AND ((doctype_id = 213207) OR (doctype_id = 403760)))
                                Rows Removed by Filter: 2
                    ->  Index Scan using participant_idx_cause_id on participant _p  (cost=0.00..155.67 rows=1 width=124) (actual time=8.382..13.921 rows=2 loops=1417)
                          Index Cond: ((cause_id = _d.cause_id) AND (cause_db_id = 2605))
              ->  Index Scan using idx_participant_type__participant_type_id on participant_type _pt  (cost=0.00..4.94 rows=1 width=60) (actual time=0.035..0.036 rows=1 loops=1)
                    Index Cond: (participant_type_id = _p.participant_type_id)
                    Filter: (((participant_type)::text !~~* '%свідок%'::text) AND (_p.participant_type_db_id = participant_type_db_id))
Total runtime: 46483.281 ms


Статистики у вас актуальные?

P.S. Это так и задумано?
Код: sql
1.
2.
3.
...
                AND (_c.number ILIKE '%Миронович%'
...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2016, 19:02
    #39171721
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Sheriffua,

это OR абстрагироваляся от вас. вернее планировщик.
и сказал вам, что до тех пор, пока у вас фильтр набора по межтабличному OR --он не сможет и не будет учитывать ваших долбанных индексов . будь они трижды триграмными.

а вот чтобы попытаться запинать [под]запрос на эти непришей индексы -- надо его развалить на UNION из 3-х не то 2-х частей.


как-то так :O]
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2016, 19:09
    #39171731
Sheriffua
Sheriffua
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
vyegorov,

Статистики каждое утро собираются:
Код: sql
1.
"%PGBIN%\vacuumdb.exe" --full --analyze



Что касается условия:
Код: sql
1.
AND (_c.number ILIKE '%Миронович%'



Оно может иметь любой набор символов, как ФИО полное так и сокращенное, просто Фамилия, или Имя отчество, не имеет значения, имеет значение, что это условие попадает автоматом для всех LIKE-ов.

Файл приложил по запросу:
Код: sql
1.
SELECT name,setting,unit FROM pg_settings WHERE source NOT IN ('default','override');
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.02.2016, 09:21
    #39171973
Sergei.Agalakov
Sergei.Agalakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Попробуйте разбить индекс cause_idx_cause_db_id_in_date на два индекса - по дате и по ID. Статистика собирается по столбцам, и комбинированные индексы могут посчитать распределение данных неверно. Пересоберите статистику и проверьте если оптимизатор выбрал лучший план.
С %asdf% вам обычные индексы не помогут. Смотрите текстовые индексы в Postgres.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса / 25 сообщений из 45, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 564ms
Закрыть
start [/forum/topic.php?fid=53&tablet=1&tid=1997429]:
 0ms
get settings:
 7ms
get forum list:
 21ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 192ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 81ms
get tp. blocked users:
 2ms
others: 239ms
total:  564ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]