Табличные индексы для столбцов массива Text[]

У меня есть таблица базы данных PostgreSQL с определенными столбцами text[] (массив). Я использую эти столбцы для поиска определенной записи в базе данных следующим образом:

select obj from business
where ((('street' = ANY (address_line_1)
    and 'a_city' = ANY (city)
    and 'a_state' = ANY (state))
or    ('street' = ANY (address_line_1)
    and '1234' = ANY (zip_code)))
and ('a_business_name' = ANY (business_name)
    or 'a_website' = ANY (website_url)
    or array['123'] && phone_numbers))

Проблема, с которой я сталкиваюсь, заключается в том, что с примерно 1 миллионом записей запрос становится очень медленным. Мой вопрос прост: столбцы массива имеют разные типы индексов? Кто-нибудь знает лучший тип индекса для создания в этом случае? (Предполагая, что существуют разные типы).

На всякий случай вот ответ explain analyze:

"Seq Scan on business  (cost=0.00..207254.51 rows=1 width=32) (actual time=18850.462..18850.462 rows=0 loops=1)"
"  Filter: (('a'::text = ANY (address_line_1)) AND (('a'::text = ANY (business_name)) OR ('a'::text = ANY (website_url)) OR ('{123}'::text[] && phone_numbers)) AND ((('a'::text = ANY (city)) AND ('a'::text = ANY (state))) OR ('1234'::text = ANY (zip_code))))"
"  Rows Removed by Filter: 900506"
"Total runtime: 18850.523 ms"

Заранее спасибо!


postgresql database-design database-indexes
person Sergio Ayestarán    schedule 28.01.2013    source источник
comment
Как всегда: номер вашей версии Postgres. Определение таблицы и некоторые примеры данных также были бы очень полезны, например, как sqlfiddle. Пример.   -  person Erwin Brandstetter    schedule 29.01.2013

Ответы (1)


arrow_upward
3
arrow_downward

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

Например:

CREATE INDEX business_address_line_1_idx ON business USING GIN (address_line_1);

Сделайте это для всех столбцов массива, участвующих в условиях.

Вместо этого, возможно, стоит подумать о нормализации вашей схемы. Возможно, вам будет удобнее разделить несколько записей в отдельную таблицу (1:n или n:m). Это часто работает в долгосрочной перспективе, даже если поначалу кажется, что это больше работы.

person Erwin Brandstetter    schedule 28.01.2013
comment
Я проголосовал за, особенно за нормализацию базы данных. IMO, полей массива следует избегать, особенно при использовании для выбора, например - person thaJeztah; 29.01.2013
comment
Спасибо, Эрвин, это сработало как волшебство: Seq Scan по делу (стоимость = 0,00..206323,42 строк = 1 ширина = 32) (фактическое время = 1259,974..1259,974 строк = 0 петель = 1) Фильтр: (('a' ::текст = ЛЮБОЙ (адрес_строка_1)) И (('a'::text = ЛЮБОЙ (название_компании)) ИЛИ ('a'::text = ЛЮБОЙ (website_url)) ИЛИ ('{123}'::text[] && phone_numbers)) И ((('a'::text = ЛЮБОЙ (город)) И ('a'::text = ЛЮБОЙ (штат)) ИЛИ ('1234'::text = ЛЮБОЙ (zip_code))) ) Строки, удаленные фильтром: 900537 Общее время выполнения: 1260,026 мс - person Sergio Ayestarán; 29.01.2013