Обсуждение: Anyway to index array with other columns

Поиск
Список
Период
Сортировка

Anyway to index array with other columns

От
Chris Hoover
Дата:
I think the answer is no, but I wanted to ask to be sure.

Given table below, is there any way to index  field_a, field_b, array_a?

Create table table1 (
field_a integer not null primary key,
field_b integer not null,
...
array_a text[]
)

My app is running queries similar to this:
Select * from table1 where field_a = 123 and field_b = 456 and array_a @> ARRAY[’tag_xyz’];

The array_a can have unto 1000 elements.  Currently these queries get very expensive if the array has any length to it.

Thanks for any ideas.

Thanks,


Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com



Re: Anyway to index array with other columns

От
"David G. Johnston"
Дата:
On Wednesday, August 2, 2023, Chris Hoover <chrish@aweber.com> wrote:
I think the answer is no, but I wanted to ask to be sure.

See btree_gin/btree_gist


David J.

Re: Anyway to index array with other columns

От
Laurenz Albe
Дата:
On Wed, 2023-08-02 at 16:17 -0400, Chris Hoover wrote:
> Given table below, is there any way to index  field_a, field_b, array_a?
>
> Create table table1 (
> field_a integer not null primary key,
> field_b integer not null,
> ...
> array_a text[]
> )
>
> My app is running queries similar to this:
> Select * from table1 where field_a = 123 and field_b = 456 and array_a @> ARRAY[’tag_xyz’];
>
> The array_a can have unto 1000 elements.  Currently these queries get very expensive if the array has any length to
it.

That seems unlikely, since you have the primary key in the WHERE condition.

Can you provide the output of EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
for the query?

Yours,
Laurenz Albe