ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
45 сообщений из 45, показаны все 2 страниц
  все  
Оптимизация запроса
    #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
10.02.2016, 11:21
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39169400
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
Неужели нет идей? может необходима дополнительная информация?
...
Рейтинг: 0 / 0
12.02.2016, 11:52
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39169427
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Sheriffua,

explain analyze запроса приведите.
...
Рейтинг: 0 / 0
12.02.2016, 12:09
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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, 12:52
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:31
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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, 16:46
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39169745
ОКТОГЕН
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ОКТОГЕН
Участник
Sheriffua,

Скрипт создания таблиц - в студию.
И результат запроса
Код: sql
1.
SELECT version();
...
Рейтинг: 0 / 0
12.02.2016, 17:01
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:11
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:18
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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, 17:54
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:09
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39169818
ОКТОГЕН
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ОКТОГЕН
Участник
qwwq,
у него всё равно like тормозит.
Вот если ещё на сервере с ресурсами беда, тогда ой...
...
Рейтинг: 0 / 0
12.02.2016, 18:12
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39169949
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
ОКТОГЕН,qwwq,

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


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

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


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

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

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

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

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



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

я сейчас обстрагировался от OR, и писал выше, что кост при этом практически не меняется. поэтому видимо "тормоз" нужно искать в другом месте.
...
Рейтинг: 0 / 0
15.02.2016, 18:45
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:01
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39171721
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Sheriffua,

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

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


как-то так :O]
...
Рейтинг: 0 / 0
15.02.2016, 19:02
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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
15.02.2016, 19:09
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39171973
Sergei.Agalakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergei.Agalakov
Участник
Попробуйте разбить индекс cause_idx_cause_db_id_in_date на два индекса - по дате и по ID. Статистика собирается по столбцам, и комбинированные индексы могут посчитать распределение данных неверно. Пересоберите статистику и проверьте если оптимизатор выбрал лучший план.
С %asdf% вам обычные индексы не помогут. Смотрите текстовые индексы в Postgres.
...
Рейтинг: 0 / 0
16.02.2016, 09:21
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172030
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Sergei.AgalakovПопробуйте разбить индекс cause_idx_cause_db_id_in_date на два индекса - по дате и по ID."вы не поняли".

селективность cause_db_id -- с гулькин чих.
это ид "базы данных".



Sergei.AgalakovС %asdf% вам обычные индексы не помогут. Смотрите текстовые индексы в Postgres."вы не поняли"

автор уже понаделал необычных триграммов. ан -- оно не подхватывается.
печаль.
[ и не должны были, из общих соображений о межтабличной структуре его OR фильтра.]
...
Рейтинг: 0 / 0
16.02.2016, 10:38
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172118
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
vyegorov,

EXPLAIN (analyze, buffers):
Код: 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.
QUERY PLAN
GroupAggregate  (cost=5488.63..5488.67 rows=1 width=312) (actual time=45844.031..45850.971 rows=1415 loops=1)
  Buffers: shared hit=19555 read=6266
  I/O Timings: read=45578.315
  ->  Sort  (cost=5488.63..5488.64 rows=1 width=312) (actual time=45844.008..45844.429 rows=2873 loops=1)
        Sort Key: _c.cause_id, _c.number, _d.judge, _d.judges, _c.in_date, _c.is_active
        Sort Method: quicksort  Memory: 947kB
        Buffers: shared hit=19555 read=6266
        I/O Timings: read=45578.315
        ->  Nested Loop  (cost=0.01..5488.62 rows=1 width=312) (actual time=57.880..45820.944 rows=2873 loops=1)
              Buffers: shared hit=19544 read=6266
              I/O Timings: read=45578.315
              ->  Nested Loop  (cost=0.01..5483.67 rows=1 width=268) (actual time=57.842..45720.910 rows=2879 loops=1)
                    Join Filter: (_c.cause_id = _p.cause_id)
                    Buffers: shared hit=13786 read=6266
                    I/O Timings: read=45578.315
                    ->  Nested Loop  (cost=0.01..5327.96 rows=1 width=161) (actual time=40.032..24485.573 rows=1417 loops=1)
                          Buffers: shared hit=8305 read=3293
                          I/O Timings: read=24402.850
                          ->  Index Scan using cause_idx_cause_db_id_in_date on cause _c  (cost=0.01..4769.78 rows=6 width=33) (actual time=0.158..6248.657 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))
                                Buffers: shared hit=198 read=962
                                I/O Timings: read=6232.860
                          ->  Index Scan using idx_document_cause on document _d  (cost=0.00..93.02 rows=1 width=132) (actual time=6.358..11.521 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
                                Buffers: shared hit=8107 read=2331
                                I/O Timings: read=18169.990
                    ->  Index Scan using participant_idx_cause_id on participant _p  (cost=0.00..155.70 rows=1 width=123) (actual time=9.126..14.978 rows=2 loops=1417)
                          Index Cond: ((cause_id = _d.cause_id) AND (cause_db_id = 2605))
                          Buffers: shared hit=5481 read=2973
                          I/O Timings: read=21175.465
              ->  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.027..0.029 rows=1 loops=2879)
                    Index Cond: (participant_type_id = _p.participant_type_id)
                    Filter: (((participant_type)::text !~~* '%свідок%'::text) AND (_p.participant_type_db_id = participant_type_db_id))
                    Rows Removed by Filter: 0
                    Buffers: shared hit=5758
Total runtime: 45851.433 ms
...
Рейтинг: 0 / 0
16.02.2016, 11:57
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172164
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Sheriffua
Код: 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.
QUERY PLAN
GroupAggregate  (cost=5488.63..5488.67 rows=1 width=312) (actual time=45844.031..45850.971 rows=1415 loops=1)
  Buffers: shared hit=19555 read=6266
  I/O Timings: read=45578.315
  ->  Sort  (cost=5488.63..5488.64 rows=1 width=312) (actual time=45844.008..45844.429 rows=2873 loops=1)
        Sort Key: _c.cause_id, _c.number, _d.judge, _d.judges, _c.in_date, _c.is_active
        Sort Method: quicksort  Memory: 947kB
        Buffers: shared hit=19555 read=6266
        I/O Timings: read=45578.315
        ->  Nested Loop  (cost=0.01..5488.62 rows=1 width=312) (actual time=57.880..45820.944 rows=2873 loops=1)
              Buffers: shared hit=19544 read=6266
              I/O Timings: read=45578.315
              ->  Nested Loop  (cost=0.01..5483.67 rows=1 width=268) (actual time=57.842..45720.910 rows=2879 loops=1)
                    Join Filter: (_c.cause_id = _p.cause_id)
                    Buffers: shared hit=13786 read=6266
                    I/O Timings: read=45578.315
                    ->  Nested Loop  (cost=0.01..5327.96 rows=1 width=161) (actual time=40.032..24485.573 rows=1417 loops=1)
                          Buffers: shared hit=8305 read=3293
                          I/O Timings: read=24402.850
                          ->  Index Scan using cause_idx_cause_db_id_in_date on cause _c  (cost=0.01..4769.78 rows=6 width=33) (actual time=0.158..6248.657 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))
                                Buffers: shared hit=198 read=962
                                I/O Timings: read=6232.860
                          ->  Index Scan using idx_document_cause on document _d  (cost=0.00..93.02 rows=1 width=132) (actual time=6.358..11.521 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
                                Buffers: shared hit=8107 read=2331
                                I/O Timings: read=18169.990
                    ->  Index Scan using participant_idx_cause_id on participant _p  (cost=0.00..155.70 rows=1 width=123) (actual time=9.126..14.978 rows=2 loops=1417)
                          Index Cond: ((cause_id = _d.cause_id) AND (cause_db_id = 2605))
                          Buffers: shared hit=5481 read=2973
                          I/O Timings: read=21175.465
              ->  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.027..0.029 rows=1 loops=2879)
                    Index Cond: (participant_type_id = _p.participant_type_id)
                    Filter: (((participant_type)::text !~~* '%свідок%'::text) AND (_p.participant_type_db_id = participant_type_db_id))
                    Rows Removed by Filter: 0
                    Buffers: shared hit=5758
Total runtime: 45851.433 ms


(Мысли вслух.)
Ну, вы сидите на IO. Первый IS, возвращающий 1582 записи (вместо ожидаемых 6) работает аж 6 секунд. Для каждой возвращаемой записи (в среднем) нужна 1 страница, 80% из которых холодные. Диси отдают страницу за 6мс, что хорошо.
Таблица с пометкой CLUSTER, но когда тот калстер был. Кажется, что данные очень фрагментированы.

А приведите, пожалуйста, вывод:
Код: sql
1.
EXPLAIN SELECT * FROM cause;



А можно сделать так (блокирующая операция!):
Код: sql
1.
2.
REINDEX TABLE cause;
CLUSTER TABLE cause USING cause_idx_cause_db_id_in_date;

и попробовать ещё раз?
...
Рейтинг: 0 / 0
16.02.2016, 12:38
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172167
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Sheriffua,

Ну вот 99% времени уходит на работу с диском. Никакие индексы тут не помогут. Да и план запроса вполне нормальный.
Или больше памяти базе выделять или ставить диски быстрее (читай ssd).
(ну или база криво настроена что тоже реальный вариант).
...
Рейтинг: 0 / 0
16.02.2016, 12:41
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172173
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Maxim Boguk...Или больше памяти базе выделять...
Кстати да! Шаренных буферов всего 32 метра — поднять бы...
...
Рейтинг: 0 / 0
16.02.2016, 12:45
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172209
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
vyegorov,

Код: sql
1.
2.
3.
EXPLAIN SELECT * FROM cause;
Результат:
Seq Scan on cause  (cost=0.00..2934368.80 rows=31156480 width=1066)
...
Рейтинг: 0 / 0
16.02.2016, 13:10
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172239
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
vyegorovMaxim Boguk...Или больше памяти базе выделять...
Кстати да! Шаренных буферов всего 32 метра — поднять бы...

Черт я был уверен что там 32GB shared_buffers.
Автор топика - вы базу что на мобильном телефоне 5 летней давности пускаете?????
И потом удивляетесь что все медленно?

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
16.02.2016, 13:29
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172381
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
vyegorovMaxim Boguk...Или больше памяти базе выделять...
Кстати да! Шаренных буферов всего 32 метра — поднять бы...

Откуда узнали что 32 метра?

P.S. Я не админ Postgresql, а пробую решить проблему с оптимизацией запроса, НО админам сообщили по поводу замечания, спасибо.
...
Рейтинг: 0 / 0
16.02.2016, 15:40
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172407
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Sheriffuavyegorovпропущено...

Кстати да! Шаренных буферов всего 32 метра — поднять бы...Откуда узнали что 32 метра?

P.S. Я не админ Postgresql, а пробую решить проблему с оптимизацией запроса, НО админам сообщили по поводу замечания, спасибо
Вы же Excel прикладывали с нестандартным конфигом (лучше текстом вставлять, через [src] и спойлер).

У вас 4096 буфера по 8Кб, вроде 32 метра выходит :)
...
Рейтинг: 0 / 0
16.02.2016, 15:59
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172492
Sergei.Agalakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergei.Agalakov
Участник
qwwqSergei.AgalakovПопробуйте разбить индекс cause_idx_cause_db_id_in_date на два индекса - по дате и по ID."вы не поняли".

селективность cause_db_id -- с гулькин чих.
это ид "базы данных".



Sergei.AgalakovС %asdf% вам обычные индексы не помогут. Смотрите текстовые индексы в Postgres."вы не поняли"

автор уже понаделал необычных триграммов. ан -- оно не подхватывается.
печаль.
[ и не должны были, из общих соображений о межтабличной структуре его OR фильтра.]
Да понял я.
С первым: ожидания оптимизатора расходятся с жизнью больше, чем на два порядка (6 против 1582). Это не есть хорошо, и я предложил путь борьбы с этим.
Со вторым: Я не спорю, что здесь надо бы union all если цель заставить искать по фамилии в первую очередь, но это для скорости запроса по сравнению с поском по номеру документа не поможет скорее всено никак. Но триграммы-то зачем? Они по настоящему нужны только если про текст мало что известно. Ну кому нужен поиск по документам для фамилии %ова% ? Какая там избирательность получится? %Петрова% по обычному текстовому индексу еще куда ни шло...
Оба моих замечания были больше концептуального порядка. Я согласен, что если б не приходилось читать с диска, то и при существующих индексах автор на скорость вряд ли стал бы жаловаться.
...
Рейтинг: 0 / 0
16.02.2016, 17:17
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172875
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
я так понимаю, что увеличение параметра shared_buffers на план запроса не влияет? т.к. админы увеличили его до 512 Мб, но изменений в плане не увидел.
...
Рейтинг: 0 / 0
17.02.2016, 10:06
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172878
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Sheriffua,

план запроса и так нормальный. сколько памяти на сервере?
...
Рейтинг: 0 / 0
17.02.2016, 10:10
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172884
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Sheriffuaя так понимаю, что увеличение параметра shared_buffers на план запроса не влияет? т.к. админы увеличили его до 512 Мб, но изменений в плане не увидел.

План у вас вполне нормальный и эффективный. Другой вопрос что он много данных с дисков поднимает и все время на это уходит, но сам план тут не причем.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
17.02.2016, 10:18
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172890
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
Alexius,

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

шаред увеличит скорость при том же плане при прогретых данных. Т.е. на первый запуск запроса не повлияет. тот как читал с диска , так и будет.


откуда мораль -- если хотите ускорить и первый запуск -- надо таки переписывать запрос, а то и реорганизовывать данные.

мелочь, которая почти не повлияет -- т.к. количество чтений останется тем же -- это уйти от дурных привычек, типа coalesce(field,value) = value -- т.к. этим вы лишаете оптимизатор возможности воспользоваться индексом, если тот есть. т.е. возможности усечь читаемое с диска множество. этой мелочевки можно ещё найти. но, до тех пор , пока вы не усечёте количество необходимых чтений -- она мало что даст.

если вот эти условия -- сильно усекающие


Код: sql
1.
2.
AND coalesce(_c.include_stat,'1') = 1
AND _c.deleted <> 1


я бы подумал о создании условного индекса с именно таким условием.
Код: sql
1.
2.
3.
4.
CREATE INDEX  ON public.cause
  USING btree (cause_db_id, in_date)
WHERE coalesce(include_stat,'1') = 1
AND deleted <> 1


(закроем глаза на coalesce, и место приращения рук кодера).
если нет -- искал бы в другом месте.

может быть даже там, где октоген, но только с ручным разбиением (по OR) на UNION.
...
Рейтинг: 0 / 0
17.02.2016, 11:29
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39173068
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
qwwq,

Если бы условие
Код: plsql
1.
AND coalesce(_c.include_stat,'1') = 1 AND _c.deleted <> 1


было селективным то в explain analyze было бы что то про:
Rows Removed by Filter: N

в блоке
Код: plaintext
1.
2.
3.
4.
5.
                          ->  Index Scan using cause_idx_cause_db_id_in_date on cause _c  (cost=0.01..4769.78 rows=6 width=33) (actual time=0.158..6248.657 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))
                                Buffers: shared hit=198 read=962
                                I/O Timings: read=6232.860

Поскольку в explain analyze ничего про это не сказано - то это условие тут на всякий случай/дырку в обоях закрыть и селективность у него нулевая.
Так что или данные в памяти или (в худшем случае на ssd) если надо чтобы быстро, а сам план вполне вменяем для такого запроса.

Автор топика явно не совсем понимает что один и тот же запрос по данным из памяти и по данным на дисках будет отличаться по скорости в 1000-100000 раз (пропорционально разницы в скорости доступа к), и в данном случае пытается искать под фонарем (план) вместо того чтобы искать там где потерял (криво настроенный/недостаточно производительный сервер).

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
17.02.2016, 12:53
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39173144
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Maxim Boguk,

вы правы, но в исходном плане таки 4 записи из 2000 этим фильтром отсечено.


если посмотреть исходный план -- основные потери на фильтрацию тут:

Код: sql
1.
2.
3.
              ->  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



т.е. нужно всё же попытаться развалить запрос на 3 (или 2) и запинать на предложенный восьмигеном триграм.

А то, что по горячим оно и так будет в 1000 раз шустрее, при достаточном шареде -- это может автора не устраивать -- у него это может быть редкий запрос, выполняемый обычно по холодным данным.
...
Рейтинг: 0 / 0
17.02.2016, 13:51
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39173145
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Исходный == 18810155
...
Рейтинг: 0 / 0
17.02.2016, 13:52
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39173220
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
Спасибо всем ответившим.
...
Рейтинг: 0 / 0
17.02.2016, 14:44
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39173365
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
eще есть варианты:
из бест практис:
1. предрасчеты по месяцам. Регламентно иметь агрегаты по всем фамилиям по месяцам.
сработает если выборки тоже по месяцам и мало изменений в прошедших периодах.
2. денормализации - втянуть в таблицу участников еще и имя судьи и с.number - тогда индекс по нему даст высокую селективность.
минус - усложнит структуру и потенциально создаст неконсистентные данные.

сомнительные варианты:
3. партицирование - только если по тем же месяцам. Тогда вместо рандомных чтений будет последовательный скан партиции
минус - постгрес и партицирование - вместе "веселей".

p.s. кстати можно заменить фильтр в участниках со '%свідок%' на список participant_type_id in (....) .
тогда вообще можно не джоинить participant_type , а participant_type_id добавить в индекс
...
Рейтинг: 0 / 0
17.02.2016, 16:57
| Ответить | Цитировать | Написать  
45 сообщений из 45, показаны все 2 страниц
  все  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=53&tid=1997429]:
 0ms
get settings:
 6ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 57ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 77ms
get tp. blocked users:
 1ms
others: 216ms
total:  380ms
созд. / загр.: 380ms
x
x
Закрыть


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