Re: Не используютя индекс

Доброго времени суток!

Вот есть проблема, может быть кто сталкивался и решал...

Есть база на mysql, я её прегнал в postgres(руками создал таблички, а
данные прегнал с помощью самописного скрипта при помощи copy). Всё
прошло успешно, все данные были прегнны в pg, но тут столкнулся с
проблемкой...

Есть одна большая таблица, порядка 3500000 записей, весит все это добро
около 2,3Gb.

В таблице есть поле:

id bigserial

по полю создан индекс:

CREATE UNIQUE INDEX test_id_index ON test (id).

Но он не используется ни при каких запросах, всегда идёт полный скан
таблицы.

Уже просто замучался...

Как "сказать" планировщику что индека нужно использовать?

С уважением.
--
Denis A. Egorov

This message is in MIME format. The first part should be readable text,
while the remaining parts are likely unreadable without MIME-aware tools.

---559023410-1144747756-1115799083=:11640
Content-Type: TEXT/PLAIN; charset=koi8-r; format=flowed
Content-Transfer-Encoding: 8BIT

Денис,

ты должен сообщить информацию
* версия PostgreSQL
* делал ли ты 'vacuum analyze' после создания индексов
* привести 'explain analyze select .....' и его выдачу

Тогда ты можешь рассчитывать, что кто-нибудь тебе поможет.

Олег
On Wed, 11 May 2005, Denis A. Egorov wrote:

> Доброго времени суток!
>
>
> Вот есть проблема, может быть кто сталкивался и решал...
>
> Есть база на mysql, я её прегнал в postgres(руками создал таблички, а
> данные прегнал с помощью самописного скрипта при помощи copy). Всё
> прошло успешно, все данные были прегнны в pg, но тут столкнулся с
> проблемкой...
>
> Есть одна большая таблица, порядка 3500000 записей, весит все это добро
> около 2,3Gb.
>
>
> В таблице есть поле:
>
> id bigserial
>
> по полю создан индекс:
>
> CREATE UNIQUE INDEX test_id_index ON test (id).
>
> Но он не используется ни при каких запросах, всегда идёт полный скан
> таблицы.
>
> Уже просто замучался...
>
> Как "сказать" планировщику что индека нужно использовать?
>
>
>
> С уважением.
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---559023410-1144747756-1115799083=:11640--

Стесняюсь спросить - а статистику ты собрал?
И пример запроса с подробностями в студию.

>Доброго времени суток!
>
>
>Вот есть проблема, может быть кто сталкивался и решал...
>
>Есть база на mysql, я её прегнал в postgres(руками создал таблички, а
>данные прегнал с помощью самописного скрипта при помощи copy). Всё
>прошло успешно, все данные были прегнны в pg, но тут столкнулся с
>проблемкой...
>
>Есть одна большая таблица, порядка 3500000 записей, весит все это добро
>около 2,3Gb.
>
>
>В таблице есть поле:
>
>id bigserial
>
>по полю создан индекс:
>
>CREATE UNIQUE INDEX test_id_index ON test (id).
>
>Но он не используется ни при каких запросах, всегда идёт полный скан
>таблицы.
>
>Уже просто замучался...
>
>Как "сказать" планировщику что индека нужно использовать?
>
>
>
>С уважением.
>
>

--
С уважением, Виктор

On Wed, May 11, 2005 at 11:09:30AM +0300, Denis A. Egorov wrote:
> Вот есть проблема, может быть кто сталкивался и решал...
>
> Есть база на mysql, я её прегнал в postgres(руками создал таблички, а
> данные прегнал с помощью самописного скрипта при помощи copy). Всё
> прошло успешно, все данные были прегнны в pg, но тут столкнулся с
> проблемкой...
>
> Есть одна большая таблица, порядка 3500000 записей, весит все это добро
> около 2,3Gb.
>
>
> В таблице есть поле:
>
> id bigserial
>
> по полю создан индекс:
>
> CREATE UNIQUE INDEX test_id_index ON test (id).
>
> Но он не используется ни при каких запросах, всегда идёт полный скан
> таблицы.

Приведи пример запроса. Насколько я помню, с bigint у постгреса
небольшая засада - в запросе параметр должен быть явно приведён к
bigint, например:

SELECT * FROM test WHERE id = 12; -- индекс не будет использоваться
SELECT * FROM test WHERE id = 12::bigint; -- должно быть OK

Докопаться до причин можно порывшись в архивах :)

--
Fduch M. Pravking

Здравствуйте, Oleg Bartunov!

Да, vacuum делал, и не раз, но что-то не помогает.

version
----------------------------------------------------------------------
PostgreSQL 7.4.7 on i386-portbld-freebsd4.11, compiled by GCC 2.95.4

explain ANALYZE SELECT id from test WHERE id=123423 limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=100000000.00..100280942.84 rows=1 width=8) (actual time=53298.684..53298.684 rows=0 loops=1)
-> Seq Scan on test (cost=100000000.00..100280942.84 rows=1 width=8) (actual time=53298.671..53298.671 rows=0 loops=1)
Filter: (id = 123423)
Total runtime: 53298.741 ms

On Wed, May 11, 2005 at 12:11:23PM +0400, you wrote:

-> Денис,
->
-> ты должен сообщить информацию
-> * версия PostgreSQL
-> * делал ли ты 'vacuum analyze' после создания индексов
-> * привести 'explain analyze select .....' и его выдачу
->
-> Тогда ты можешь рассчитывать, что кто-нибудь тебе поможет.
->
-> Олег
-> On Wed, 11 May 2005, Denis A. Egorov wrote:
->
-> >Доброго времени суток!
-> >
-> >
-> >Вот есть проблема, может быть кто сталкивался и решал...
-> >
-> >Есть база на mysql, я её прегнал в postgres(руками создал таблички, а
-> >данные прегнал с помощью самописного скрипта при помощи copy). Всё
-> >прошло успешно, все данные были прегнны в pg, но тут столкнулся с
-> >проблемкой...
-> >
-> >Есть одна большая таблица, порядка 3500000 записей, весит все это добро
-> >около 2,3Gb.
-> >
-> >
-> >В таблице есть поле:
-> >
-> >id bigserial
-> >
-> >по полю создан индекс:
-> >
-> >CREATE UNIQUE INDEX test_id_index ON test (id).
-> >
-> >Но он не используется ни при каких запросах, всегда идёт полный скан
-> >таблицы.
-> >
-> >Уже просто замучался...
-> >
-> >Как "сказать" планировщику что индека нужно использовать?
-> >
-> >
-> >
-> >С уважением.
-> >
->
-> Regards,
-> Oleg
-> _____________________________________________________________
-> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
-> Sternberg Astronomical Institute, Moscow University (Russia)
-> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
-> phone: +007(095)939-16-83, +007(095)939-23-83

--
Denis A. Egorov

Здравствуйте, Alexander M. Pravking!

Да! :)

Вы оказались совешенно правы!

Всё работает когда поставил приведение к типу.

Теперь возникае другой вопрос: это что теперь нужно визде ставить
привидение?

On Wed, May 11, 2005 at 12:44:22PM +0400, you wrote:

-> On Wed, May 11, 2005 at 11:09:30AM +0300, Denis A. Egorov wrote:
-> > Вот есть проблема, может быть кто сталкивался и решал...
-> >
-> > Есть база на mysql, я её прегнал в postgres(руками создал таблички, а
-> > данные прегнал с помощью самописного скрипта при помощи copy). Всё
-> > прошло успешно, все данные были прегнны в pg, но тут столкнулся с
-> > проблемкой...
-> >
-> > Есть одна большая таблица, порядка 3500000 записей, весит все это добро
-> > около 2,3Gb.
-> >
-> >
-> > В таблице есть поле:
-> >
-> > id bigserial
-> >
-> > по полю создан индекс:
-> >
-> > CREATE UNIQUE INDEX test_id_index ON test (id).
-> >
-> > Но он не используется ни при каких запросах, всегда идёт полный скан
-> > таблицы.
->
-> Приведи пример запроса. Насколько я помню, с bigint у постгреса
-> небольшая засада - в запросе параметр должен быть явно приведён к
-> bigint, например:
->
-> SELECT * FROM test WHERE id = 12; -- индекс не будет использоваться
-> SELECT * FROM test WHERE id = 12::bigint; -- должно быть OK
->
-> Докопаться до причин можно порывшись в архивах :)
->
-> --
-> Fduch M. Pravking
->

--
Denis A. Egorov

This message is in MIME format. The first part should be readable text,
while the remaining parts are likely unreadable without MIME-aware tools.

---559023410-1594243340-1115802954=:11640
Content-Type: TEXT/PLAIN; charset=koi8-r; format=flowed
Content-Transfer-Encoding: 8BIT

Обновись до 8.02 или явно делай cast
explain ANALYZE SELECT id from test WHERE id=123423::bigint limit 1;

Олег
On Wed, 11 May 2005, Denis A. Egorov wrote:

> Здравствуйте, Oleg Bartunov!
>
> Да, vacuum делал, и не раз, но что-то не помогает.
>
> version
> ----------------------------------------------------------------------
> PostgreSQL 7.4.7 on i386-portbld-freebsd4.11, compiled by GCC 2.95.4
>
>
>
> explain ANALYZE SELECT id from test WHERE id=123423 limit 1;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------
> Limit (cost=100000000.00..100280942.84 rows=1 width=8) (actual time=53298.684..53298.684 rows=0 loops=1)
> -> Seq Scan on test (cost=100000000.00..100280942.84 rows=1 width=8) (actual time=53298.671..53298.671 rows=0 loops=1)
> Filter: (id = 123423)
> Total runtime: 53298.741 ms
>
>
>
> On Wed, May 11, 2005 at 12:11:23PM +0400, you wrote:
>
> -> Денис,
> ->
> -> ты должен сообщить информацию
> -> * версия PostgreSQL
> -> * делал ли ты 'vacuum analyze' после создания индексов
> -> * привести 'explain analyze select .....' и его выдачу
> ->
> -> Тогда ты можешь рассчитывать, что кто-нибудь тебе поможет.
> ->
> -> Олег
> -> On Wed, 11 May 2005, Denis A. Egorov wrote:
> ->
> -> >Доброго времени суток!
> -> >
> -> >
> -> >Вот есть проблема, может быть кто сталкивался и решал...
> -> >
> -> >Есть база на mysql, я её прегнал в postgres(руками создал таблички, а
> -> >данные прегнал с помощью самописного скрипта при помощи copy). Всё
> -> >прошло успешно, все данные были прегнны в pg, но тут столкнулся с
> -> >проблемкой...
> -> >
> -> >Есть одна большая таблица, порядка 3500000 записей, весит все это добро
> -> >около 2,3Gb.
> -> >
> -> >
> -> >В таблице есть поле:
> -> >
> -> >id bigserial
> -> >
> -> >по полю создан индекс:
> -> >
> -> >CREATE UNIQUE INDEX test_id_index ON test (id).
> -> >
> -> >Но он не используется ни при каких запросах, всегда идёт полный скан
> -> >таблицы.
> -> >
> -> >Уже просто замучался...
> -> >
> -> >Как "сказать" планировщику что индека нужно использовать?
> -> >
> -> >
> -> >
> -> >С уважением.
> -> >
> ->
> -> Regards,
> -> Oleg
> -> _____________________________________________________________
> -> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> -> Sternberg Astronomical Institute, Moscow University (Russia)
> -> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> -> phone: +007(095)939-16-83, +007(095)939-23-83
>
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---559023410-1594243340-1115802954=:11640--

On Wed, May 11, 2005 at 11:52:02AM +0300, Denis A. Egorov wrote:
> Да! :)
>
> Вы оказались совешенно правы!
>
> Всё работает когда поставил приведение к типу.
>
> Теперь возникае другой вопрос: это что теперь нужно визде ставить
> привидение?

Скорее всего, везде, где идёт сравнение значения столбца с константой
или выражением, результатом которого является что-то отличное от bigint.
JOIN'ы по двум bigint-столбцам должны отрабатывать на ура.

Другой вариант (если 2G значений достаточно) - заменить bigserial на
serial.

Наконец, наилучший вариант, как уже посоветовал Олег, - обновиться до
8.02.

--
Fduch M. Pravking

Доброго времени суток!

Все огромное спасибо, за решение проблемы, ставлю 8-ку, буду
тетировать...

Но вот нашёл такую вот особенность, что если делать

SELECT ... ib='12121'

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

А в доках про такое ни где не написано.

On Wed, May 11, 2005 at 11:09:30AM +0300, you wrote:

->
->
-> Вот есть проблема, может быть кто сталкивался и решал...
->
-> Есть база на mysql, я её прегнал в postgres(руками создал таблички, а
-> данные прегнал с помощью самописного скрипта при помощи copy). Всё
-> прошло успешно, все данные были прегнны в pg, но тут столкнулся с
-> проблемкой...
->
-> Есть одна большая таблица, порядка 3500000 записей, весит все это добро
-> около 2,3Gb.
->
->
-> В таблице есть поле:
->
-> id bigserial
->
-> по полю создан индекс:
->
-> CREATE UNIQUE INDEX test_id_index ON test (id).
->
-> Но он не используется ни при каких запросах, всегда идёт полный скан
-> таблицы.
->
-> Уже просто замучался...
->
-> Как "сказать" планировщику что индека нужно использовать?
->
->
->

С уважением.
--
Denis A. Egorov

On Wed, May 11, 2005 at 05:21:10PM +0300, Denis A. Egorov wrote:
> Но вот нашёл такую вот особенность, что если делать
>
> SELECT ... ib='12121'
>
> то приведение не нужно, а индекс используется...
>
> А в доках про такое ни где не написано.

В общем-то, закавыченные константы - это правильно :)

Насколько я знаю, закавыченная константа изначально имеет псевдотип
unknown и всегда приводится к типу того, с чем она сравнивается.
Константы вида 12.34 изначально numeric, вида 1234 - integer или bigint
в зависимости от того, влезает она в signed int4 или нет. И получалось,
что для сравнения в данном случае значения bigint-столбца с
int4-константой значение столбца неявно приводилось к int4, и индекс в
этом случае не мог быть использован. (Странно, но сейчас попробовал на
7.4.7 построить индекс по (id::integer), но он тоже не используется -
возможно, я неправ. Пусть меня поправят, если что.)

В восьмёрке же Tom похимичил над неявным приведением типов, и проблема
решилась. Кстати, была она не только с bigint.

> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Самый нужный tip ;)

--
Fduch M. Pravking