Message-ID: <c33f14e7990deba1020eaf568cf6c1182d3e63b3.camel@cybertec.at>
Subject: Re: Anyway to index array with other columns
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Chris Hoover <chrish@aweber.com>, pgsql-admin@lists.postgresql.org
Date: Thu, 03 Aug 2023 08:30:44 +0200
In-Reply-To: <C17CF1E6-B89A-4BBF-8821-11E559B523D9@aweber.com>
References: <C17CF1E6-B89A-4BBF-8821-11E559B523D9@aweber.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/c33f14e7990deba1020eaf568cf6c1182d3e63b3.camel%40cybertec.at>
Precedence: bulk

On Wed, 2023-08-02 at 16:17 -0400, Chris Hoover wrote:
> Given table below, is there any way to index =C2=A0field_a, field_b, arra=
y_a?
>=20
> Create table table1 (
> field_a integer not null primary key,
> field_b integer not null,
> ...
> array_a text[]
> )
>=20
> My app is running queries similar to this:
> Select * from table1 where field_a =3D 123 and field_b =3D 456 and array_=
a=C2=A0@> ARRAY[=E2=80=99tag_xyz=E2=80=99];
>=20
> The array_a can have unto 1000 elements. =C2=A0Currently these queries ge=
t 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