Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

Не работает индекс

Posted Июнь 2nd, 2016 by NEW_Squamis
in

Добрый день!
У меня возникла проблема с использованием индекса в партиционной таблице.
Есть 3 таблицы: Партии, Продукты, Материалы.
Партии:

CREATE TABLE v4.headers (
  fldidxheader BIGINT DEFAULT NEXTVAL('v4.headers_fldidxheader_seq'::regclass) NOT NULL,
  fldhdr_nr VARCHAR(255) NOT NULL,
  CONSTRAINT headers_pkey PRIMARY KEY(fldidxheader)
) WITH (oids = false);

Продукты:
CREATE TABLE v4.products (
  fldidxproduct BIGINT DEFAULT NEXTVAL('v4.products_fldidxproduct_seq'::regclass) NOT NULL,
  fldidxheader BIGINT [] DEFAULT '{-1,-2,-3,-4,-5}'::bigint[] NOT NULL,
  CONSTRAINT products_pkey PRIMARY KEY(fldidxproduct)
) WITH (oids = false);

Материалы (главная таблица):
CREATE TABLE v4.products$elements (
  fldidxelement BIGINT DEFAULT NEXTVAL('v4."products$elements_fldidxelement_seq"'::regclass) NOT NULL
) WITH (oids = false);

Материалы (inherit таблица (15 штук)):
CREATE TABLE inheritss.products$elements$00000000 (
  CONSTRAINT products$elements$00000000_pkey PRIMARY KEY(fldidxelement),
  CONSTRAINT products$elements$00000000_chk CHECK ((fldidxproduct >= 0) AND (fldidxproduct <= 99999)),
  CONSTRAINT products$elements_chk CHECK (fldel_qty > 0)
) INHERITS (v4.products$elements) WITH (oids = false);
 
CREATE INDEX products$elements$00000000_fldidxproduct_idx ON inheritss.products$elements$00000000 USING btree (fldidxproduct);

Запрос 1:

SELECT
  hdr.fldidxheader,
  prd.fldidxproduct,
  pe.fldidxelement
FROM
  v4.headers                        hdr
  INNER JOIN v4.products            prd ON (ARRAY[hdr.fldidxheader] <@ prd.fldidxheader)
  INNER JOIN v4."products$elements" pe  ON (prd.fldidxproduct = pe.fldidxproduct)
WHERE
  hdr.fldidxheader = 17171

План запроса:
"QUERY PLAN"
"Sort  (cost=1314296.40..1314802.01 rows=202244 width=24)"
"  Sort Key: prd.fldidxproduct, pe.fldidxelement"
"  ->  Hash Join  (cost=15008.82..1292321.90 rows=202244 width=24)"
"        Hash Cond: (pe.fldidxproduct = prd.fldidxproduct)"
"        ->  Append  (cost=0.00..1123611.55 rows=40447756 width=16)"
"              ->  Seq Scan on ""products$elements"" pe  (cost=0.00..0.00 rows=1 width=16)"
"              ->  Seq Scan on ""products$elements$00000000"" pe_1  (cost=0.00..95960.38 rows=3464238 width=16)"
"              ->  Seq Scan on ""products$elements$00100000"" pe_2  (cost=0.00..108336.00 rows=3903500 width=16)"
"              ->  Seq Scan on ""products$elements$00200000"" pe_3  (cost=0.00..109973.77 rows=3960277 width=16)"
"              ->  Seq Scan on ""products$elements$00300000"" pe_4  (cost=0.00..14330.45 rows=515545 width=16)"
"              ->  Seq Scan on ""products$elements$00400000"" pe_5  (cost=0.00..109696.14 rows=3947914 width=16)"
"              ->  Seq Scan on ""products$elements$00500000"" pe_6  (cost=0.00..110351.01 rows=3972401 width=16)"
"              ->  Seq Scan on ""products$elements$00600000"" pe_7  (cost=0.00..107581.49 rows=3870749 width=16)"
"              ->  Seq Scan on ""products$elements$00700000"" pe_8  (cost=0.00..103614.94 rows=3731394 width=16)"
"              ->  Seq Scan on ""products$elements$00800000"" pe_9  (cost=0.00..91688.75 rows=3299675 width=16)"
"              ->  Seq Scan on ""products$elements$00900000"" pe_10  (cost=0.00..102764.36 rows=3692436 width=16)"
"              ->  Seq Scan on ""products$elements$01000000"" pe_11  (cost=0.00..100127.05 rows=3601305 width=16)"
"              ->  Seq Scan on ""products$elements$01100000"" pe_12  (cost=0.00..69187.21 rows=2488321 width=16)"
"        ->  Hash  (cost=14946.50..14946.50 rows=4986 width=16)"
"              ->  Nested Loop  (cost=90.93..14946.50 rows=4986 width=16)"
"                    ->  Index Only Scan using headers_pkey on headers hdr  (cost=0.29..8.31 rows=1 width=8)"
"                          Index Cond: (fldidxheader = 17171)"
"                    ->  Bitmap Heap Scan on products prd  (cost=90.64..14888.33 rows=4986 width=192)"
"                          Recheck Cond: (ARRAY[hdr.fldidxheader] <@ fldidxheader)"
"                          ->  Bitmap Index Scan on products_idx2  (cost=0.00..89.39 rows=4986 width=0)"
"                                Index Cond: (ARRAY[hdr.fldidxheader] <@ fldidxheader)"

Запрос не хочет использовать индекс в inherit таблицах по полю fldidxproduct

Запрос 2:

SELECT
  hdr.fldidxheader,
  prd.fldidxproduct,
  pe.fldidxelement
FROM
  v4.headers                        hdr
  INNER JOIN v4.products            prd ON (hdr.fldidxheader = ANY(prd.fldidxheader))
  INNER JOIN v4."products$elements" pe  ON (prd.fldidxproduct = pe.fldidxproduct)
WHERE
  hdr.fldidxheader = 17171

План запроса 2:
"QUERY PLAN"
"Sort  (cost=246590.50..246604.80 rows=5719 width=24)"
"  Sort Key: prd.fldidxproduct, pe.fldidxelement"
"  ->  Nested Loop  (cost=0.29..246233.59 rows=5719 width=24)"
"        ->  Nested Loop  (cost=0.29..84799.53 rows=141 width=16)"
"              Join Filter: (hdr.fldidxheader = ANY (prd.fldidxheader))"
"              ->  Index Only Scan using headers_pkey on headers hdr  (cost=0.29..8.31 rows=1 width=8)"
"                    Index Cond: (fldidxheader = 17171)"
"              ->  Seq Scan on products prd  (cost=0.00..62354.76 rows=997176 width=192)"
"        ->  Append  (cost=0.00..1130.04 rows=1488 width=16)"
"              ->  Seq Scan on ""products$elements"" pe  (cost=0.00..0.00 rows=1 width=16)"
"                    Filter: (prd.fldidxproduct = fldidxproduct)"
"              ->  Index Scan using ""products$elements$00000000_fldidxproduct_idx"" on ""products$elements$00000000"" pe_1  (cost=0.43..92.95 rows=122 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$00100000_fldidxproduct_idx"" on ""products$elements$00100000"" pe_2  (cost=0.43..103.18 rows=135 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$00200000_fldidxproduct_idx"" on ""products$elements$00200000"" pe_3  (cost=0.43..104.82 rows=137 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$00300000_fldidxproduct_idx"" on ""products$elements$00300000"" pe_4  (cost=0.42..62.52 rows=81 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$00400000_fldidxproduct_idx"" on ""products$elements$00400000"" pe_5  (cost=0.43..105.74 rows=138 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$00500000_fldidxproduct_idx"" on ""products$elements$00500000"" pe_6  (cost=0.43..96.54 rows=131 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$00600000_fldidxproduct_idx"" on ""products$elements$00600000"" pe_7  (cost=0.43..100.59 rows=133 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$00700000_fldidxproduct_idx"" on ""products$elements$00700000"" pe_8  (cost=0.43..94.03 rows=123 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$00800000_fldidxproduct_idx"" on ""products$elements$00800000"" pe_9  (cost=0.43..93.50 rows=122 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$00900000_fldidxproduct_idx"" on ""products$elements$00900000"" pe_10  (cost=0.43..102.17 rows=137 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$01000000_fldidxproduct_idx"" on ""products$elements$01000000"" pe_11  (cost=0.43..95.67 rows=125 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"
"              ->  Index Scan using ""products$elements$01100000_fldidxproduct_idx"" on ""products$elements$01100000"" pe_12  (cost=0.43..78.32 rows=103 width=16)"
"                    Index Cond: (fldidxproduct = prd.fldidxproduct)"

Запрос не хочет использовать индекс в таблице products по полю fldidxproduct

Вопрос:
Как "заставить" планировщик использовать индексы без принудительного SET enable_seqscan=off; (Хотя и при нем тоже не хочет)

Помогите разобраться, пожалуйста!
Заранее спасибо!

‹ Не могу изменить поля в NEW в триггерной ф-ии. Редактирование значений тегов и атрибутов поля типа XML ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Сервер сам знает как ему лучше.

On Июнь 2nd, 2016 alexekoz says:

Я конечно не ас в оптимизации, но на сколько я знаю, в некоторых случаях сервер предпочитает не использовать идекс, например когда в любом случае надо пройтись по всей таблице, тогда он идет не по индексу. Есть еще куча случаев когда сервер принимает не то решение, как ожидалось. Можно попробовать использовать предикаты в запросе вместо join, у меня бывали случае когда в зависимости от того что я использую join или предикаты менялся план запроса.

Вот нашел.

On Июнь 3rd, 2016 alexekoz says:

Читай здесь вопрос "Почему мои запросы работают медленно? Почему они не используют мои индексы?"

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023