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

Ответ 1

Если вы хотите, чтобы индекс TEXT индексировал запросы LIKE, вам необходимо создать его с помощью text_pattern_ops, например:

test=> CREATE TABLE t AS SELECT n::TEXT FROM generate_series( 1,100000 ) n;
test=> CREATE INDEX tn ON t(n);
test=> VACUUM ANALYZE t;
test=> EXPLAIN ANALYZE SELECT * FROM t WHERE n LIKE '123%';
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=10 width=5) (actual time=0.027..14.631 rows=111 loops=1)
   Filter: (n ~~ '123%'::text)
 Total runtime: 14.664 ms

test=> CREATE INDEX tn2 ON t(n text_pattern_ops);
CREATE INDEX
Temps : 267,589 ms
test=> EXPLAIN ANALYZE SELECT * FROM t WHERE n LIKE '123%';
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=5.25..244.79 rows=10 width=5) (actual time=0.089..0.121 rows=111 loops=1)
   Filter: (n ~~ '123%'::text)
   ->  Bitmap Index Scan on tn2  (cost=0.00..5.25 rows=99 width=0) (actual time=0.077..0.077 rows=111 loops=1)
         Index Cond: ((n ~>=~ '123'::text) AND (n ~<~ '124'::text))
 Total runtime: 0.158 ms

см. здесь:

http://www.postgresql.org/docs/9.1/static/indexes-opclass.html

Если вы не хотите создавать дополнительный индекс, а столбец - это ТЕКСТ, вы можете заменить "compte LIKE" 6% "на" compte >= '6 "и compte <' 7 '", который является простое условие диапазона индексов.

test=> EXPLAIN ANALYZE SELECT * FROM t WHERE n >= '123' AND n < '124';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Index Scan using tn on t  (cost=0.00..126.74 rows=99 width=5) (actual time=0.030..0.127 rows=111 loops=1)
   Index Cond: ((n >= '123'::text) AND (n < '124'::text))
 Total runtime: 0.153 ms

В вашем случае это решение, вероятно, лучше.

Ответ 2

Похоже, что СУБД оценивает, что JOIN на assentament будет намного более ограничительным, чем фильтрация компиляторов, а затем объединение.

Параметры могут быть... 1. Поместите индекс на assentaments.compte_id
2. Измените свой индекс на comptes, чтобы включить id в качестве первого проиндексированного поля.

Первая опция может разрешить выполнение плана выполнения: Filter comptes, а затем присоединяться к assentament.

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

Ответ 3

Это чаще всего связано с плохой статистикой индекса, т.е. если индекс недостаточно избирательный (например, много повторяющихся значений), доступ и фильтрация по индексу могут быть еще более трудоемкими, чем выполнение seq-сканирования.

Являются ли ваши значения на c.codi_compte достаточно избирательными? Может быть, у вас слишком много нулевых значений?

Ответ 4

Я бы попробовал

• составной индекс (data, compte_id) в таблице assentaments и

• составной индекс (empresa_id, nivell, codi_compte, id) в таблице comptes

Вы также должны включить LEFT JOIN в INNER JOIN. Условия WHERE, которые вы сделали, эквивалентны. Возможно, планировщик запросов не знает об этом.

Другое подозрение - тип поля comptes.codi_compte. Если это integer, а не char(), то

WHERE c.codi_compte LIKE '6%'

переводится как:

WHERE CAST(c.codi_compte AS CHAR) LIKE '6%'

что означает, что индекс не может быть использован. В этом случае вы можете преобразовать поле в тип char.

Ответ 5

Есть несколько вещей, которые вы могли бы сделать. Во-первых:

SELECT SUM(a.deure)-SUM(a.haver) as Value

SUM() коснется каждой строки, которая не соответствует INDEX этой операции.

FROM assentaments a, comptes c

При отладке запросов мне проще использовать естественный JOIN вместо явного JOIN. Планировщик запросов освобождается немного больше и часто делает выбор лучше. Однако это не тот случай, просто общий комментарий. Здесь, где есть вероятные несоответствия между вашими INDEX es и вашим запросом.

WHERE TRUE = TRUE
    AND a.compte_id  = c.id
    AND c.empresa_id = 2
    AND c.nivell     = 11

Из этих трех запросов у вас есть следующий INDEX:

CREATE INDEX "index_multiple" ON "public". "comptes" ИСПОЛЬЗОВАНИЕ btree (codi_compte ASC NULLS LAST, empresa_id ASC NULLS LAST, nivell ASC NULLS LAST);

Разделите это, так как это не UNIQUE INDEX, вы не должны видеть никаких изменений в целостности ваших данных. Причина, по которой я предлагаю это, состоит в том, что я предполагаю, что codi_compte имеет низкую мощность. Я предполагаю, что empresa_id будет иметь более высокую мощность. В общем, создайте свои INDEX es от наивысшей мощности до самого низкого.

Я подозреваю, что три INDEX es будут делать битмап-соединение или хеш-соединение быстрее. Суть проблемы в том, что PostgreSQL (возможно, правильно) считает, что выполнение index_scan более дорогое, чем выполнение seq_scan.

    AND (a.data >='2007-01-01' AND a.data <='2007-01-31')
    AND c.codi_compte LIKE '6%';

An INDEX on a.data также может быть полезен, потому что PostgreSQL скорее всего сделает index_scan в дате, указанной в зависимости от количества строк в таблице assentaments.

CREATE INDEX "index_codi_compte" ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST);
CREATE INDEX "index_comptes"     ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST);

Я не знаю, почему у вас есть этот INDEX дважды.

CREATE INDEX "index_multiple" ON "public"."comptes" USING btree(codi_compte ASC NULLS LAST,     empresa_id ASC NULLS LAST, nivell ASC NULLS LAST);

Как описано выше, сломайте < <22 > .

CREATE INDEX "index_nivell" ON "public"."comptes" USING btree(nivell ASC NULLS LAST);

То, что INDEX отлично.

Быстрый совет:

SELECT matching, total, matching / total AS "Want this to be a small number"
FROM
    (SELECT count(*)::FLOAT AS matching FROM tbl WHERE col_id = 1) AS matching,
    (SELECT count(*)::FLOAT AS total FROM tbl) AS total;


 matching rows | total rows | want this to be a small number 
---------------+------------+--------------------------------
             1 |         10 |                            0.1
(1 row)

Если третий столбец идеально равен 1/total.