Критически долго выполняется запрос

Posted Август 31st, 2017 by pixel

Вопросы по SQL и другие аспекты работы PostgreSQL

Есть таблица со множеством полей типа:

CREATE TABLE big_data
(
  id serial NOT NULL,
  ...
  is_active BOOLEAN NOT NULL,
  source_id integer NOT NULL,
  CONSTRAINT big_data_pkey PRIMARY KEY (id)
);

Есть общий индекс btree на is_active и source_id.

Запрос во вновь созданной таблице на 7 млн. сгенерированных записей

SELECT id FROM big_data WHERE is_active IS TRUE AND source_id=2 ORDER BY id DESC LIMIT 1;

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

В чем может быть причина? Или как можно диагностировать проблему?

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

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

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

Сделайте для начала VACUUM

On Август 31st, 2017 admin says:

Сделайте для начала VACUUM ANALYZE на таблицу

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

Что выдаст: 1 EXPLAIN SELECT

On Август 31st, 2017 altair says:

Что выдаст:
1

EXPLAIN
SELECT id FROM big_data WHERE is_active IS TRUE AND source_id=2 ORDER BY id DESC LIMIT 1;

EXPLAIN ANALYSE
SELECT id FROM big_data WHERE is_active IS TRUE AND source_id=2 ORDER BY id DESC LIMIT 1;

SELECT count(*) FROM big_data WHERE is_active IS TRUE AND source_id=2;

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

Большое спасибо за участие!

On Сентябрь 2nd, 2017 pixel says:

Вроде немного проясняется. Всё дело в том, что есть 2 таблицы. Одна с рабочими данными, а другая тестовая для опытов.
Рабочая:

CREATE TABLE public."big_data"
(
  "id" INTEGER NOT NULL DEFAULT NEXTVAL((pg_get_serial_sequence('"public"."big_data"'::text, 'id'::text))::regclass),
  ...
  source_id SMALLINT,
  is_active BOOLEAN DEFAULT TRUE,
  CONSTRAINT "pbig_data" PRIMARY KEY ("id")
)
WITH (
  OIDS=FALSE
);
CREATE INDEX "ibig_data-source_id__is_active"
  ON public."big_data"
  USING btree
  (source_id, is_active)
  WHERE source_id IS NOT NULL AND is_active;

и та на которой я всё тестирую, заполненная примерно таким же количеством данных:

CREATE TABLE public.big_data
(
  id INTEGER NOT NULL DEFAULT NEXTVAL('big_data_id_seq'::regclass),
  text_data text,
  is_active BOOLEAN DEFAULT TRUE,
  source_id SMALLINT,
  CONSTRAINT big_data_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
CREATE INDEX big_data_source_id_index
  ON public.big_data
  USING btree
  (source_id, is_active)
  WHERE source_id IS NOT NULL AND is_active;

Так вот. Один и тот же запрос:

EXPLAIN SELECT
    "id"
FROM
    "big_data"
WHERE
    source_id = 6
    AND is_active
ORDER BY "id" DESC
LIMIT 1

и
EXPLAIN SELECT id FROM big_data WHERE is_active AND source_id=2 ORDER BY id DESC LIMIT 1;
даёт разные результаты - в одном индекс используется, в другом - нет:

"Limit  (cost=0.43..220.64 rows=1 width=4)"
"  ->  Index Scan Backward using "pbig_data" on "big_data"  (cost=0.43..10254585.72 rows=46567 width=4)"
"        Filter: (is_active AND (source_id = 6))"

"Limit  (cost=11.26..11.26 rows=1 width=4)"
"  ->  Sort  (cost=11.26..11.26 rows=3 width=4)"
"        Sort Key: id DESC"
"        ->  Bitmap Heap Scan on big_data  (cost=4.13..11.24 rows=3 width=4)"
"              Recheck Cond: ((source_id = 2) AND is_active)"
"              ->  Bitmap Index Scan on big_data_source_id_index  (cost=0.00..4.13 rows=3 width=0)"
"                    Index Cond: (source_id = 2)"

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

Навигация

Реклама

Вход для пользователей