Как заставить Postgres использовать определенный индекс / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как заставить Postgres использовать определенный индекс

25 сообщений из 25, страница 1 из 1

Как заставить Postgres использовать определенный индекс

#39656648

polin11

Гость

Есть запрос

Код: sql

1.
2.
3.
4.
5.
6.

SELECT DISTINCT
"Field1"
FROM
"Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2' 
LIMIT 100

В базе есть индекс по полю Field1 и составной индекс по 2 полям
(Field2 и Field3).
Если в запросе указать ограничение LIMIT, то используется индекс
по полю Field1 и потребляется много ресурсов.
Если в запросе убрать ограничение LIMIT, то используется составной
индекс (Field2 и Field3) ресурсов тратиться в 2 раза меньше, но
время выполнения запроса в несколько раз больше.
Вопрос можно ли в запросе оставить LIMIT 100 и
обязать Postgres использовать составной индекс?

...

Рейтинг:

0 / 0

06.06.2018, 16:23

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39656739

polin11

Гость

Например, если изменить запрос на

Код: sql

1.
2.
3.
4.
5.
6.

SELECT DISTINCT
"Field1",  "Field2"
FROM
"Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2' 
LIMIT 100

то используется составной индекс (Field2 и Field3), но время выполнения также увеличивается в несколько раз

...

Рейтинг:

0 / 0

06.06.2018, 17:27

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39656741

ну я

Участник

Откуда: Москва

Сообщения: 1 270

Рейтинг: 0 / 0

polin11,

Что если добавить в запрос order by и поля по которым надо использовать индекс?

...

Рейтинг:

0 / 0

06.06.2018, 17:30

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39656754

polin11

Гость

Если добавить ORDER BY и дополнительные поля:

Код: sql

1.
2.
3.
4.
5.
6.
7.

SELECT DISTINCT
"Field1",  "Field2", "Field3" 
FROM
"Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2' 
ORDER BY "Field2", "Field3"
LIMIT 100

то используется составной индекс, но время выполнения в несколько раз больше

...

Рейтинг:

0 / 0

06.06.2018, 17:41

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39656756

polin11

Гость

даже если проставить LIMIT 200000, то используется составной индекс

...

Рейтинг:

0 / 0

06.06.2018, 17:42

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39656814

ну я

Участник

Откуда: Москва

Сообщения: 1 270

Рейтинг: 0 / 0

polin11,

Похоже что его оптимизатор видит order by по индексу для выполнения distinct, но рвется на limit.

Вот такой

explain select field1 from ( select distinct field1 from ttt where field1='val1' and field2 like 'val2%' order by field1) as tmp limit 100;

по идее должен отдать первые distinct сортированные по индексу но второй отсечь по лимиту 100.
explain показывает малые числа. Данных для проверки не имею, только схему.

...

Рейтинг:

0 / 0

06.06.2018, 18:24

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39656955

polin11

Гость

К сожалению, используется индекс только по полю Field1. В данном случае, видимо, стоит выбор между быстродействием и ресурсоемкостью

...

Рейтинг:

0 / 0

06.06.2018, 22:53

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39656960

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

polin11К сожалению, используется индекс только по полю Field1. В данном случае, видимо, стоит выбор между быстродействием и ресурсоемкостью

Если вам нужно быстродействие этого запроса - сделайте правильный индекс под этот запрос вида:
create index someindexname on "Table"("Field3", "Field2" text_pattern_ops);
(если "Field2" varchar тогда вместо text_pattern_ops сделать varchar_pattern_ops)

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

06.06.2018, 23:16

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39656973

polin11

Гость

К сожалению даже такой сокращенный запрос

Код: sql

1.
2.
3.
4.

SELECT DISTINCT "Field1"
FROM "Table"
WHERE "Field2" LIKE 'val1%'  
LIMIT 100

Планировщик использует индекс по полю Field1, а не индекс по полю Field2.
Fileld2 поле типа text, индекс добавлен с классом операторов text_pattern_ops.

Для примера , если изменить запрос

Код: sql

1.
2.
3.
4.

SELECT DISTINCT "Field1", "Field2"
FROM "Table"
WHERE "Field2" LIKE 'val1%'  
LIMIT 100

или

Код: sql

1.
2.
3.

SELECT DISTINCT "Field1"
FROM "Table"
WHERE "Field2" LIKE 'val1%'

даже такой запрос, с другим фильтром "Field2" = 'val1'

Код: sql

1.
2.
3.
4.

SELECT DISTINCT "Field1"
FROM "Table"
WHERE "Field2" = 'val1' 
LIMIT 100

То во всех случаях используется индекс по полю Field2, но в этих случаях время выполнения запроса слишком большое

...

Рейтинг:

0 / 0

07.06.2018, 01:47

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39658760

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Maxim Bogukpolin11К сожалению, используется индекс только по полю Field1. В данном случае, видимо, стоит выбор между быстродействием и ресурсоемкостью

Если вам нужно быстродействие этого запроса - сделайте правильный индекс под этот запрос вида:
create index someindexname on "Table"("Field3", "Field2" text_pattern_ops);
(если "Field2" varchar тогда вместо text_pattern_ops сделать varchar_pattern_ops)

--
Maxim Boguk
dataegret.ru

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

наврал. достаточно луз-индекскана по полю 1 . с экзистсом по лайку на поле 2 по составному с лидирующим 1-м и опсами на 2--м. если кардинальности подходящие.

наверное можете пополнить вашу коллекцию трюков

т.е. бартунов неуиноватый.

...

Рейтинг:

0 / 0

09.06.2018, 10:53

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39659030

polin11

Гость

qwwqMaxim Bogukпропущено...

Если вам нужно быстродействие этого запроса - сделайте правильный индекс под этот запрос вида:
create index someindexname on "Table"("Field3", "Field2" text_pattern_ops);
(если "Field2" varchar тогда вместо text_pattern_ops сделать varchar_pattern_ops)

--
Maxim Boguk
dataegret.ru

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

наврал. достаточно луз-индекскана по полю 1 . с экзистсом по лайку на поле 2 по составному с лидирующим 1-м и опсами на 2--м. если кардинальности подходящие.

наверное можете пополнить вашу коллекцию трюков

т.е. бартунов неуиноватый.

Из описанного выше, только понял, что ген. дир. Postgres Professional Бартунов не виновен.
Ваш совет по структуре индексов не смог интерпретировать.
Для запроса

Код: sql

1.
2.
3.
4.

 SELECT DISTINCT "Field1"
FROM "Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2' 
LIMIT 100

Поле Field1 тип bigint, по нему есть индекс, при выполнении этого запроса он и используется ( Index Scan using "iTable-Field1" on "Table" ).
Поле Filed2 типа text, поле Fileld3 типа bigint. Есть индекс SearchFields составной состоит из поля Field2 с типом операторов text_pattern_ops, и поля Field3. Если убрать из запроса LIMIT 100, то используется Bitmap Index Scan on "iTable-SearchFields". Ресурсов тратиться меньше, но времени больше.
Поясните подробно, что нужно сделать с индексами, по Вашему мнению?

...

Рейтинг:

0 / 0

09.06.2018, 17:20

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39659142

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

polin11,

"мэньше" "болше" -- ви што, штаны меряете ?

план запросав рисуйте давайте с цифиркой -- скока менше , скока больше.

а про реализацию луз-индекс-сканов для дистинктов тут уже много где понаписано. поищите. loose
http://www.sql.ru/forum/actualsearch.aspx?search=loose index scan&sin=0&bid=7&a=&ma=0&dt=-1&s=1&so=1

скушно всё одно и то же по 100-му разу накручивать.
опять же в статистику и соотношения кардинальностей упираемся.

и да , я со слепу не всё увидел, для луза видимо вам потребуется индекс (Ф3,Ф1,Ф2 с опсами)
и прямые руки
как проваливаться на второй уровень в рекурсии луза (при редком первом или константе как у вас) я где-то недавно писал. но так ли оно по мощностям -- надо ваш случай смотреть .
а будет ли пресловутый опс упираться отсутствием отношения порядка (недоступностью вернее) выведенного наружу -- надо смотреть по мере реализации . тогда и решим -- увиновин рафик или неувиновин в данном конкретном.

если писать сложного луза руками не хотите -- сделайте или (Ф3,Ф2 с опсами) как кто-то выше подбросил. именно в таком порядке.
или простой луз по Ф1 с речеком условия. (и помянутый индекс все равно не помешает)

а вообще без планов и цифр -- это гадание какое-то

...

Рейтинг:

0 / 0

09.06.2018, 23:36

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39659147

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

polin11,

Для начала показать что дают 3 запроса:

1)SELECT count(*) FROM "Table";

2)SELECT count(*) FROM "Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2'

3)SELECT count(DISTINCT "Field1")
FROM
"Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2'

А там подумаем.

Все советы исходили из того что WHERE условие у вас высокоселективное (т.е. под него попадает условно 0.1% таблицы или меньше).

Если это не так - то задача:
а)вообще странная очень
б)не имеет эффективного решения с помощью индексов без полной переделки запроса

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

10.06.2018, 00:16

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39659200

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Maxim Boguk,
из того, что при лимите "запрос считается быстро" -- селективность самого Ф1 хороша. и/или порядок скорее всего далёк от кластерного вдоль оного.

"ресурсов больше" --WTF -- скорее всего параллел запускается. (так отключить его перед запросом и делов)

раз Ф3 упорно суётся последним -- оно малоселективное, и ,согласно дебильному поверию, индекс с него лучше не начинать (алфавитный указатель не должен начинаться с буквы раздела . ага. д.б.)

даже булево Ф3 перед Ф1 снизит расходы по задаче вдвое (при пополамном распределении) . а после -- будет болтаться как г-но в проруби, разве что для иос пригодится.

полюбасу индекс (Ф3,Ф1,Ф2опс) для данной задачи с лимитом -- оптимален. даже если луза не реализовывать. (для речека тоже)

...

Рейтинг:

0 / 0

10.06.2018, 10:54

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660115

polin11

Гость

Интересное наблюдение:
1) Если записей удовлетворяющих условию "Field2" LIKE 'val1%' AND "Field3" ='val2' меньше 3000, то
используется составной индекс, если больше, то индекс по полю Field1.
2) Если из запроса убрать DISTINCT и записей удовлетворяющих условию "Field2" LIKE 'val1%' AND "Field3" ='val2' меньше 1 млн., то также используется составной индекс.
3) Если из запроса убрать DISTINCT и записей удовлетворяющих условию больше 1 млн., то вообще используется Seq scan

...

Рейтинг:

0 / 0

13.06.2018, 10:53

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660129

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

polin11Интересное наблюдение:
1) Если записей удовлетворяющих условию "Field2" LIKE 'val1%' AND "Field3" ='val2' меньше 3000, то
используется составной индекс, если больше, то индекс по полю Field1.
2) Если из запроса убрать DISTINCT и записей удовлетворяющих условию "Field2" LIKE 'val1%' AND "Field3" ='val2' меньше 1 млн., то также используется составной индекс.
3) Если из запроса убрать DISTINCT и записей удовлетворяющих условию больше 1 млн., то вообще используется Seq scan

цифры где ?

умеет ли дон експлейн аналайз ?

или вы так, погулять пришли

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

...

Рейтинг:

0 / 0

13.06.2018, 11:12

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660173

polin11

Гость

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

"Limit  (cost=0.43..23.42 rows=20 width=8) (actual time=656.420..656.601 rows=20 loops=1)"
"  Buffers: shared hit=276383 read=29294"
"  ->  Unique  (cost=0.43..185111.08 rows=161054 width=8) (actual time=656.418..656.598 rows=20 loops=1)"
"        Buffers: shared hit=276383 read=29294"
"        ->  Index Scan using "iField1" on "Table"  (cost=0.43..184549.42 rows=224666 width=8) (actual time=656.416..656.591 rows=26 loops=1)"
"              Filter: (("Field2 ~~ 'на%'::text) AND ("Field3" = 81))"
"              Rows Removed by Filter: 2136847"
"              Buffers: shared hit=276383 read=29294"
"Planning time: 0.390 ms"
"Execution time: 656.635 ms"

Не устаивает Shared hit memory 2.1Gb

...

Рейтинг:

0 / 0

13.06.2018, 12:05

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660552

polin11

Гость

Maxim Bogukpolin11,

Для начала показать что дают 3 запроса:

1)SELECT count(*) FROM "Table";

2)SELECT count(*) FROM "Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2'

3)SELECT count(DISTINCT "Field1")
FROM
"Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2'

А там подумаем.

Все советы исходили из того что WHERE условие у вас высокоселективное (т.е. под него попадает условно 0.1% таблицы или меньше).

Если это не так - то задача:
а)вообще странная очень
б)не имеет эффективного решения с помощью индексов без полной переделки запроса

--
Maxim Boguk
dataegret.ru

SELECT count(*) FROM "Table"
15910261

SELECT count(*) FROM "Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2'
1103549

SELECT count(DISTINCT "Field1")
FROM
"Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2'
1029457

...

Рейтинг:

0 / 0

14.06.2018, 00:27

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660554

polin11

Гость

qwwqMaxim Boguk,
из того, что при лимите "запрос считается быстро" -- селективность самого Ф1 хороша. и/или порядок скорее всего далёк от кластерного вдоль оного.

"ресурсов больше" --WTF -- скорее всего параллел запускается. (так отключить его перед запросом и делов)

раз Ф3 упорно суётся последним -- оно малоселективное, и ,согласно дебильному поверию, индекс с него лучше не начинать (алфавитный указатель не должен начинаться с буквы раздела . ага. д.б.)

даже булево Ф3 перед Ф1 снизит расходы по задаче вдвое (при пополамном распределении) . а после -- будет болтаться как г-но в проруби, разве что для иос пригодится.

полюбасу индекс (Ф3,Ф1,Ф2опс) для данной задачи с лимитом -- оптимален. даже если луза не реализовывать. (для речека тоже)

Сделал индекс (Field3,Field1,Field2 ops), ситуация аналогичная при запросе

Код: sql

1.
2.
3.
4.
5.
6.

SELECT DISTINCT
"Field1"
FROM
"Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2' 
LIMIT 100

используется индекс по Field1, если уберу LIMIT 100, используется вновь созданный индекс (Field3,Field1,Field2 ops)

...

Рейтинг:

0 / 0

14.06.2018, 00:31

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660557

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

polin11SELECT count(*) FROM "Table"
15910261

SELECT count(*) FROM "Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2'
1103549

SELECT count(DISTINCT "Field1")
FROM
"Table"
WHERE "Field2" LIKE 'val1%' AND "Field3" ='val2'
1029457

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

Это как раз про:

Если это не так - то задача:
а)вообще странная очень
б)не имеет эффективного решения с помощью индексов без полной переделки запроса

Хотите быстро подобный запрос - делайте индекс только по (Field3, Field1) и используйте loose index scan технику 100 раз описанную на этом форуме.
В 101 раз я ее писать не буду потому что лень.

PS: использование LIMIT N без ORDER BY мягко говоря еще один показатель что вы не очень понимаете чего вам надо именно от базы.

PPS: если вы считаете что база может используя индексы этот запрос подсчитать быстро - объясните какой алгоритм она должна использовать для этого.

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

14.06.2018, 00:43

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660684

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Maxim Boguk,

1. я ошибся -- по числу хитов вероятнее всего табличка почти кластеризована по Ф1.
2. его изначальных индексов (и возможно цтид) видимо достаточно для луза . (не хватает данных о мощности самого Ф1).

и я таки накидал луз для кейса г-на безручко:

data

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

CREATE TABLE table_
(
	id serial primary key
	,field1 integer
	,field2 text
	,field3 boolean not null

);
insert into table_
select g, g -( g % 5), trim((g % 5)::text)||'_txt', (g % 2)::BOOLEAN from generate_series(0,1900000) g
;
CREATE INDEX ON table_ (field3,field1,field2 text_pattern_ops);

QRY

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

with recursive 
t as 
(
(
SELECt FIELD1, 0 as n --,*
FRom table_
WHERE Field2 LIKE '1_%' AND Field3 =True
order by field3,field1 limit 1
)
union all 
select l.field1, n+1 as n
from t
,lateral (select 
FIELD1
FRom table_ tt
WHERE tt.field1> t.field1
AND Field2 LIKE '1_%' AND Field3 =True
order by field3,field1 limit 1
) l
where n <99
) 
select * from t

explain

Код: 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.

'CTE Scan on t  (cost=38.94..39.56 rows=31 width=8) (actual time=0.038..2.668 rows=100 loops=1)'
'  Output: t.field1, t.n'
'  Buffers: shared hit=402'
'  CTE t'
'    ->  Recursive Union  (cost=0.43..38.94 rows=31 width=8) (actual time=0.034..2.604 rows=100 loops=1)'
'          Buffers: shared hit=402'
'          ->  Subquery Scan on "*SELECT* 1"  (cost=0.43..0.78 rows=1 width=8) (actual time=0.033..0.034 rows=1 loops=1)'
'                Output: "*SELECT* 1".field1, 0'
'                Buffers: shared hit=4'
'                ->  Limit  (cost=0.43..0.77 rows=1 width=9) (actual time=0.031..0.031 rows=1 loops=1)'
'                      Output: table_.field1, 0, table_.field3'
'                      Buffers: shared hit=4'
'                      ->  Index Only Scan using table__field3_field1_field2_idx on public.table_  (cost=0.43..64164.67 rows=188496 width=9) (actual time=0.030..0.030 rows=1 loops=1)'
'                            Output: table_.field1, 0, table_.field3'
'                            Index Cond: ((table_.field3 = true) AND (table_.field2 ~>=~ '1'::text) AND (table_.field2 ~<~ '2'::text))'
'                            Filter: (table_.field3 AND (table_.field2 ~~ '1_%'::text))'
'                            Heap Fetches: 1'
'                            Buffers: shared hit=4'
'          ->  Nested Loop  (cost=0.43..3.76 rows=3 width=8) (actual time=0.024..0.025 rows=1 loops=100)'
'                Output: tt.field1, (t_1.n + 1)'
'                Buffers: shared hit=398'
'                ->  WorkTable Scan on t t_1  (cost=0.00..0.22 rows=3 width=8) (actual time=0.000..0.000 rows=1 loops=100)'
'                      Output: t_1.field1, t_1.n'
'                      Filter: (t_1.n < 99)'
'                      Rows Removed by Filter: 0'
'                ->  Limit  (cost=0.43..1.15 rows=1 width=5) (actual time=0.024..0.024 rows=1 loops=99)'
'                      Output: tt.field1, tt.field3'
'                      Buffers: shared hit=398'
'                      ->  Index Only Scan using table__field3_field1_field2_idx on public.table_ tt  (cost=0.43..45664.95 rows=62832 width=5) (actual time=0.023..0.023 rows=1 loops=99)'
'                            Output: tt.field1, tt.field3'
'                            Index Cond: ((tt.field3 = true) AND (tt.field1 > t_1.field1) AND (tt.field2 ~>=~ '1'::text) AND (tt.field2 ~<~ '2'::text))'
'                            Filter: (tt.field3 AND (tt.field2 ~~ '1_%'::text))'
'                            Heap Fetches: 99'
'                            Buffers: shared hit=398'
'Planning time: 0.544 ms'
'Execution time: 2.791 ms'

...

Рейтинг:

0 / 0

14.06.2018, 11:44

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660753

polin11

Гость

qwwq,

Хочу в начале, выразить Вам благодарность, что тратите личное время на мою проблему и подробно описываете Ваши действия. Спасибо большое!!!
Сделал как вы сказали, немного модифицировал ваш запрос

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

with recursive 
t as (
(
	SELECt "Field1", 0 as n
	FRom "Table"
	WHERE "Field2" LIKE 'ст%' AND "Field3" =81
	order by "Field3","Field1" limit 1
)
union all 
	select l."Field1", n+1 as n
	from t	,lateral 
	(select 	"Field1"
	FRom "Table" tt
	WHERE tt."Field1"> t."Field1"
	AND "Field2" LIKE 'ст%' AND "Field3" =81
	order by "Field3","Field1" limit 1
	) l
where n <99
) 
select  distinct * from t
order by "Field1"

Создал индекс CREATE INDEX ON table_ (field3,field1,field2 text_pattern_ops);
Все равно планировщик не использует этот индекс, берет индекс по Field1

Код: 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.

"Unique  (cost=46.51..46.74 rows=31 width=12) (actual time=758.965..758.981 rows=100 loops=1)"
"  Buffers: shared hit=276686 read=29382 written=49"
"  CTE t"
"    ->  Recursive Union  (cost=0.43..45.12 rows=31 width=12) (actual time=757.311..758.896 rows=100 loops=1)"
"          Buffers: shared hit=276680 read=29382 written=49"
"          ->  Subquery Scan on "*SELECT* 1"  (cost=0.43..1.26 rows=1 width=12) (actual time=757.309..757.310 rows=1 loops=1)"
"                Buffers: shared hit=276381 read=29275 written=49"
"                ->  Limit  (cost=0.43..1.25 rows=1 width=20) (actual time=757.309..757.309 rows=1 loops=1)"
"                      Buffers: shared hit=276381 read=29275 written=49"
"                      ->  Index Scan using "iField1" on "Table"  (cost=0.43..184549.42 rows=224666 width=20) (actual time=757.306..757.306 rows=1 loops=1)"
"                            Filter: (("Field2" ~~ 'ст%'::text) AND ("Field3" = 81))"
"                            Rows Removed by Filter: 2136796"
"                            Buffers: shared hit=276381 read=29275 written=49"
"          ->  Nested Loop  (cost=0.43..4.32 rows=3 width=12) (actual time=0.015..0.015 rows=1 loops=100)"
"                Buffers: shared hit=299 read=107"
"                ->  WorkTable Scan on t t_1  (cost=0.00..0.22 rows=3 width=12) (actual time=0.000..0.000 rows=1 loops=100)"
"                      Filter: (n < 99)"
"                      Rows Removed by Filter: 0"
"                ->  Limit  (cost=0.43..1.34 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=99)"
"                      Buffers: shared hit=299 read=107"
"                      ->  Index Scan using "iField1" on "Table" tt  (cost=0.43..68453.35 rows=74889 width=16) (actual time=0.012..0.012 rows=1 loops=99)"
"                            Index Cond: ("Field1" > t_1."Field1")"
"                            Filter: (("Field2" ~~ 'ст%'::text) AND ("Field3" = 81))"
"                            Rows Removed by Filter: 1"
"                            Buffers: shared hit=299 read=107"
"  ->  Sort  (cost=1.39..1.47 rows=31 width=12) (actual time=758.964..758.969 rows=100 loops=1)"
"        Sort Key: t."Field1", t.n"
"        Sort Method: quicksort  Memory: 29kB"
"        Buffers: shared hit=276686 read=29382 written=49"
"        ->  CTE Scan on t  (cost=0.00..0.62 rows=31 width=12) (actual time=757.313..758.918 rows=100 loops=1)"
"              Buffers: shared hit=276680 read=29382 written=49"
"Planning time: 4.765 ms"
"Execution time: 759.052 ms"

Вот если удалить индекс по Field1, то используется созданный индекс и запрос использует намного меньше ресурсов:

Код: 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.

"Unique  (cost=66.04..66.27 rows=31 width=12) (actual time=0.875..0.889 rows=100 loops=1)"
"  Buffers: shared hit=503"
"  CTE t"
"    ->  Recursive Union  (cost=0.56..64.65 rows=31 width=12) (actual time=0.018..0.847 rows=100 loops=1)"
"          Buffers: shared hit=503"
"          ->  Subquery Scan on "*SELECT* 1"  (cost=0.56..1.39 rows=1 width=12) (actual time=0.017..0.018 rows=1 loops=1)"
"                Buffers: shared hit=5"
"                ->  Limit  (cost=0.56..1.38 rows=1 width=20) (actual time=0.017..0.017 rows=1 loops=1)"
"                      Buffers: shared hit=5"
"                      ->  Index Only Scan using "table__field3_field1_field2_idx" on "Table"  (cost=0.56..184774.55 rows=224666 width=20) (actual time=0.016..0.016 rows=1 loops=1)"
"                            Index Cond: (("Field3" = 81) AND ("Field2" ~>=~ 'ст'::text) AND ("Field2" ~<~ 'су'::text))"
"                            Filter: ("Field2" ~~ 'ст%'::text)"
"                            Heap Fetches: 1"
"                            Buffers: shared hit=5"
"          ->  Nested Loop  (cost=0.56..6.27 rows=3 width=12) (actual time=0.007..0.007 rows=1 loops=100)"
"                Buffers: shared hit=498"
"                ->  WorkTable Scan on t t_1  (cost=0.00..0.22 rows=3 width=12) (actual time=0.000..0.000 rows=1 loops=100)"
"                      Filter: (n < 99)"
"                      Rows Removed by Filter: 0"
"                ->  Limit  (cost=0.56..1.99 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=99)"
"                      Buffers: shared hit=498"
"                      ->  Index Only Scan using "table__field3_field1_field2_idx" on "Table" tt  (cost=0.56..107546.43 rows=74889 width=16) (actual time=0.005..0.005 rows=1 loops=99)"
"                            Index Cond: (("Field3" = 81) AND ("Field1" > t_1."Field1") AND ("Field2" ~>=~ 'ст'::text) AND ("Field2" ~<~ 'су'::text))"
"                            Filter: ("Field2" ~~ 'ст%'::text)"
"                            Heap Fetches: 99"
"                            Buffers: shared hit=498"
"  ->  Sort  (cost=1.39..1.47 rows=31 width=12) (actual time=0.874..0.877 rows=100 loops=1)"
"        Sort Key: t."Field1", t.n"
"        Sort Method: quicksort  Memory: 29kB"
"        Buffers: shared hit=503"
"        ->  CTE Scan on t  (cost=0.00..0.62 rows=31 width=12) (actual time=0.020..0.863 rows=100 loops=1)"
"              Buffers: shared hit=503"
"Planning time: 0.968 ms"
"Execution time: 0.928 ms"

...

Рейтинг:

0 / 0

14.06.2018, 13:39

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660813

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

polin11,

1. не понял -- нахера у вас в выводе стоит дистинкт и ордер бай. они самим рекурсивным цте обеспечиваются.

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

например сделать один из 2-х функциональным, и дурить грёбаный планировщик грёбаного пж.

* пс. радует, что в том же оракле года 4 назад я видел рукопашный луз--индекскан. т.е. тут везде на математиксах сэкономили. даже жадный ларри.

...

Рейтинг:

0 / 0

14.06.2018, 14:58

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660843

polin11

Гость

qwwqpolin11,

1. не понял -- нахера у вас в выводе стоит дистинкт и ордер бай. они самим рекурсивным цте обеспечиваются.

Да, дистинкт и ордер бай не нужны.
Буду думать как совместить индексы, так сказать, перефразируя Пушкина в одну телегу впрячь
коня и трепетную лань.

...

Рейтинг:

0 / 0

14.06.2018, 15:47

| Ответить | Цитировать | Написать

Как заставить Postgres использовать определенный индекс

#39660885

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

polin11qwwqpolin11,

1. не понял -- нахера у вас в выводе стоит дистинкт и ордер бай. они самим рекурсивным цте обеспечиваются.

Да, дистинкт и ордер бай не нужны.
Буду думать как совместить индексы, так сказать, перефразируя Пушкина в одну телегу впрячь
коня и трепетную лань.

попробуйте вместо
CREATE INDEX ON table_ (field3,field1,field2 text_pattern_ops);
сделать
CREATE INDEX ON table_ (field3,field1);

и посмотреть будет ли такой индекс использоваться автоматически.
Если нет то можно как уже написали сделать функциональный индекс и запрос под него переписать но это конечно криво.

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

14.06.2018, 16:56

| Ответить | Цитировать | Написать

25 сообщений из 25, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как заставить Postgres использовать определенный индекс

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=39660554&tid=1995728]:	0ms
get settings:	12ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	43ms
get topic data:	11ms
get forum data:	3ms
get page messages:	52ms
get tp. blocked users:	1ms
others:	14ms

total:	158ms