ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Не используется индекс или как ускорить?
11 сообщений из 11, страница 1 из 1
Не используется индекс или как ускорить?
    #33289648
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
Есть таблица ma_data. В ней около 300 тыс строк.
Есть в ней поле alias_id. Все NOT NULL и всего их разных 32.
Есть индекс по этому полю(btree). Но запросы по типу:
Код: plaintext
1.
2.
3.
4.
5.
SELECT alias_id FROM ma_data GROUP BY alias_id

или

SELECT DISTINCT alias_id FROM ma_data
этот индекс не использую, предпочитая seq scan. При отключении seq scan получаем чуть быстрее, но все равно проход ВСЕЙ ТАБЛИЦЫ.
Код: plaintext
1.
2.
3.
 HashAggregate  (cost= 38565 . 30 .. 38565 . 62  rows= 32  width= 4 ) (actual time= 11777 . 2 .. 11777 . 651  rows= 32  loops= 1 )
   ->  Seq Scan on ma_data  (cost= 0 . 00 .. 37811 . 24  rows= 301624  width= 4 ) (actual time= 0 . 045 .. 10425 . 205  rows= 301624  loops= 1 )
 Total runtime:  11783 . 264  ms

Код: plaintext
1.
2.
3.
4.
 Group  (cost= 0 . 00 .. 1140280 . 63  rows= 32  width= 4 ) (actual time= 0 . 159 .. 2640 . 090  rows= 32  loops= 1 )
   ->  Index Scan using reference_9_fk on ma_data  (cost= 0 . 00 .. 1139526 . 57  rows= 301624  width= 4 ) (actual time= 0 . 120 .. 1471 . 128  rows= 301624  loops= 1 )
 Total runtime:  2640 . 407  ms
( 3  rows)
Т.е. ясно, что это справочник и те же значения можно получить в другом место, но вопрос ПОЧЕМУ происходит именно так?
Да статистику установил в 998 :)
Код: plaintext
1.
2.
ALTER TABLE "public"."ma_data"
  ALTER COLUMN "alias_id" SET STATISTICS  998 ;
...
Рейтинг: 0 / 0
26.09.2005, 22:47
| Ответить | Цитировать | Написать  
Не используется индекс или как ускорить?
    #33289959
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Andrey Daeron
... но все равно проход ВСЕЙ ТАБЛИЦЫ

Так у тебя же запрос на проход всей таблицы :-) Да, ты выбираешь только различные alias_id,
но чтобы собрать все различные, приходиться просматривать все (чтобы ничего не пропустить :-).
Я могу ошибаться, но единственный способ здесь - отдельная таблица, обновляемая триггерами
(как это не печально) (приемлимо если изменения таблицы ma-data нечасто происходят (не с частотой 1 Гц) :-)
Я могу ошибаться.
...
Рейтинг: 0 / 0
27.09.2005, 10:02
| Ответить | Цитировать | Написать  
Не используется индекс или как ускорить?
    #33290233
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
Funny_Falcon Andrey Daeron
... но все равно проход ВСЕЙ ТАБЛИЦЫ

Так у тебя же запрос на проход всей таблицы :-) Да, ты выбираешь только различные alias_id,
но чтобы собрать все различные, приходиться просматривать все (чтобы ничего не пропустить :-).
Я могу ошибаться, но единственный способ здесь - отдельная таблица, обновляемая триггерами
(как это не печально) (приемлимо если изменения таблицы ma-data нечасто происходят (не с частотой 1 Гц) :-)
Я могу ошибаться.блинн, ну ясно же, что оптимизатор - кг/ам . Ибо:
Код: plaintext
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.
EXPLAIN ANALYZE (SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 1  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 2  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 3  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 4  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 5  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 6  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 7  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 8  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 9  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 10  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 11  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 12  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 13  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 14  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 15  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 16  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 17  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 18  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 19  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 20  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 21  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 22  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 23  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 24  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 25  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 26  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 27  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 28  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 29  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 30  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 31  LIMIT  1 )
UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id = 32  LIMIT  1 )
)
даст вам считанные мс. Так что просто напишите ф-ю вида:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
CREATE OR REPLACE FUNCTION public.f_distinct_alias_id()
  RETURNS SETOF int4 AS
'DECLARE
	min_a int4;
	max_a int4;
	iid int4;
	vrec  int4; --RECORD;
BEGIN
	min_a := (SELECT alias_id FROM ma_data ORDER BY alias_id LIMIT 1);
	max_a := (SELECT alias_id FROM ma_data ORDER BY alias_id DESC LIMIT 1);
	FOR iid IN  min_a..max_a
	LOOP
		SELECT INTO vrec alias_id FROM ma_data WHERE alias_id = iid  LIMIT 1;
		RETURN NEXT vrec;
	END LOOP;
	
RETURN;
END;
'
  LANGUAGE 'plpgsql' STABLE STRICT;
и буит вам щасье:
Код: plaintext
1.
2.
3.
4.
 SELECT * FROM f_distinct_alias_id();

Суммарное время выполнения запроса: 47  ms.
Время получения данных: 0  ms.
получено строк:  32
...
Рейтинг: 0 / 0
27.09.2005, 11:30
| Ответить | Цитировать | Написать  
Не используется индекс или как ускорить?
    #33290254
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
	LOOP
		SELECT INTO vrec alias_id FROM ma_data
			WHERE alias_id = iid  LIMIT  1 ;
		IF NOT vrec IS NULL 
		THEN
			RETURN NEXT vrec;
		END IF;
	END LOOP;
на усяк случай поправляюсь
...
Рейтинг: 0 / 0
27.09.2005, 11:37
| Ответить | Цитировать | Написать  
Не используется индекс или как ускорить?
    #33290423
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
4321блинн, ну ясно же, что оптимизатор - кг/ам . Ибо:
[src]EXPLAIN ANALYZE (SELECT (SELECT alias_id FROM ma_data WHERE alias_id =1 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =2 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =32 LIMIT 1)

Вот надыбал в рассылке постгреса. Грустно, но факт.
With a query of the form:

select field,count([field|*]) from table WHERE somefield =
somecondition;

the query planner is going to have to scan every single row returned by
that where clause. There's no shortcut, because the visibility rules of
MVCC means you have to look at every tuple IN THE TABLE, not in the
index (it's the way postgresql is built, and it isn't likely to change
soon, because putting the visibility information in indexes is
expensive, and would result in VERY slow updates and very large
indexes).

So, the best optimization is to use a selective where clause.

If you run the query with a where clause of something like:

where processdate between '01 july 2005' and '07 july 2005'

then you should get better performance.
...
Рейтинг: 0 / 0
27.09.2005, 12:21
| Ответить | Цитировать | Написать  
Не используется индекс или как ускорить?
    #33290462
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
Funny_Falcon Andrey Daeron
... но все равно проход ВСЕЙ ТАБЛИЦЫ

Так у тебя же запрос на проход всей таблицы :-) Да, ты выбираешь только различные alias_id,
но чтобы собрать все различные, приходиться просматривать все (чтобы ничего не пропустить :-).
Я могу ошибаться, но единственный способ здесь - отдельная таблица, обновляемая триггерами
(как это не печально) (приемлимо если изменения таблицы ma-data нечасто происходят (не с частотой 1 Гц) :-)
Я могу ошибаться.
Обьновления могут быть и чаще. До 30-40 инсертов в секунду, и слава богу практически не бывает апдейтов.
Решение нашлось просто - по скольку БД нормализирована, то есть справочник с соответсвующими полями, ну а дальше индексируемый JOIN. В общем, все решилось. Но сам факт удручает. В файрбердах таки используется индекс :(
...
Рейтинг: 0 / 0
27.09.2005, 12:29
| Ответить | Цитировать | Написать  
Не используется индекс или как ускорить?
    #33290574
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
Andrey Daeron есть справочник с соответсвующими полями, ну а дальше индексируемый JOIN приведите ДЖОНА, т.к. я не сообразил влёт, как его прикрутить.

Т.к. из 2х вариаций
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
EXPLAIN ANALYZE  SELECT alias.alias_id   
FROM alias INNER JOIN ma_data 
ON alias.alias_id = ma_data.alias_id


EXPLAIN ANALYZE  SELECT alias.alias_id   
FROM alias WHERE EXISTS( SELECT * FROM  ma_data  WHERE
 alias.alias_id = ma_data.alias_id)
2-й у меня выполняется на порядки быстрее (постгрес 7.4)
...
Рейтинг: 0 / 0
27.09.2005, 12:56
| Ответить | Цитировать | Написать  
Не используется индекс или как ускорить?
    #33290899
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
4321 Andrey Daeron есть справочник с соответсвующими полями, ну а дальше индексируемый JOIN приведите ДЖОНА, т.к. я не сообразил влёт, как его прикрутить.

Подло наврал :) JoIN был в другом месте. Здесь примерно так:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
    SELECT aa.alias_id AS ad, aa.endperiod AS ep
    FROM (
        SELECT ma_alias.id AS alias_id, (
            SELECT ma2.endperiod
            FROM ma_data ma2
            WHERE (ma2.alias_id = ma_alias.id)
            ORDER BY ma2.endperiod
            LIMIT  1 
            ) AS endperiod
        FROM ma_alias
        ) aa
    WHERE (aa.alias_id <>  0 )
...
Рейтинг: 0 / 0
27.09.2005, 14:22
| Ответить | Цитировать | Написать  
Не используется индекс или как ускорить?
    #33290908
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
4321блинн, ну ясно же, что оптимизатор - кг/ам.
Блин, ты прав.
Еще один трабл - если при первом обращении оптимизатор для SELECT ... WHERE alias_id = iid
LIMIT 1 выберет seqscan, он его будет применять ко всем остальным. В одной задаче это вызвало
жуткие тормоза. Поэтому пришлось сделать запрос динамическим. (по моему так, не помню точно:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
CREATE OR REPLACE FUNCTION public.f_distinct_alias_id()
  RETURNS SETOF int4 AS
$$
DECLARE
	min_a int4;
	max_a int4;
	iid int4;
	vrec  int4; --RECORD;
BEGIN
	min_a := (SELECT alias_id FROM ma_data ORDER BY alias_id LIMIT  1 );
	max_a := (SELECT alias_id FROM ma_data ORDER BY alias_id DESC LIMIT  1 );
	FOR iid IN  min_a..max_a
	LOOP
       		FOR vrec in EXECUTE 'SELECT INTO vrec alias_id FROM ma_data WHERE alias_id =' || iid || ' LIMIT 1' 
                LOOP
		     IF vrec IS NOT NULL THEN
                          RETURN NEXT vrec;
                     END IF
                END LOOP
	END LOOP;
	
RETURN;
END;
$$
  LANGUAGE 'plpgsql' STABLE STRICT;
)
К стати, расшифруй кг/ам (некоторых общеупотребительных сокращений я не знаю :-)
...
Рейтинг: 0 / 0
27.09.2005, 14:23
| Ответить | Цитировать | Написать  
Не используется индекс или как ускорить?
    #33291020
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
Funny_Falcon К стати, расшифруй кг/ам (некоторых общеупотребительных сокращений я не знаю :-) этто из репертуара т.н. "падонкав". Примерно так: "креатиф-ф-ф - г-но/ аффтар - м-к". Жестко, но касательно поведения оптимизатора для этого случая - применимо. В принципе оптимизатор для индексируемого поля для дистинкта по нему должен выполнять что-то типа
Код: plaintext
1.
2.
SELECT index.alias_id   
FROM index WHERE EXISTS( SELECT * FROM  "table"  WHERE
 index.alias_id = table.alias_id)
(в силу того, что индекс постгреса не знает о своей актуальности) - тогда вопросов к нему бы не было.

А так - либо приходится пользоваться тем, что есть справочник (всех возможных значений индекса), либо тем, что существует известный дискретный набор возможных значений (для целых типов). К сожалению это не всегда выполняется.
...
Рейтинг: 0 / 0
27.09.2005, 14:51
| Ответить | Цитировать | Написать  
Не используется индекс или как ускорить?
    #33291094
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
1.конечно же оптимизатор "должен" так поступать только в случае низкой селективности индекса

, и

2. конечно же именно индекс "знает" весь набор допустимых значений.
...
Рейтинг: 0 / 0
27.09.2005, 15:06
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Не используется индекс или как ускорить?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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