Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > DROP TABLE IF EXISTS t ; > > CREATE TABLE t > ( > id integer /* PRIMARY KEY */, > stuff integer, > large_array_of_values float[] > ) ; > > CREATE OR REPLACE FUNCTION random_vector() RETURNS float[] AS > $$ > select > array_agg(random()) > from > generate_series (1, 1000) > $$ > LANGUAGE SQL ; > > > INSERT INTO t > (id, stuff, large_array_of_values) > SELECT > id, random()*10000, random_vector() > FROM > generate_series(1, 10000) AS i(id) ; > > CREATE INDEX > idx_32 ON t(id, (large_array_of_values[32])); > > <pre> > ✓ > > ✓ > > ✓ > 10000 rows affected > > ✓ > </pre> <!-- --> > EXPLAIN ANALYZE > SELECT > avg(large_array_of_values[32]) > FROM > t > WHERE > id BETWEEN 5000 and 7500 > AND (large_array_of_values[32]) > 0.32 ; > > <pre> > | QUERY PLAN | > | :---------------------------------------------------------------------------------------------------------------------- | > | Aggregate (cost=46.94..46.95 rows=1 width=8) (actual time=54.871..54.871 rows=1 loops=1) | > | -&gt; Bitmap Heap Scan on t (cost=4.91..46.89 rows=17 width=32) (actual time=0.392..1.204 rows=1732 loops=1) | > | Recheck Cond: ((id &gt;= 5000) AND (id &lt;= 7500) AND (large_array_of_values[32] &gt; '0.32'::double precision)) | > | Heap Blocks: exact=20 | > | -&gt; Bitmap Index Scan on idx_32 (cost=0.00..4.91 rows=17 width=0) (actual time=0.364..0.364 rows=1732 loops=1) | > | Index Cond: ((id &gt;= 5000) AND (id &lt;= 7500) AND (large_array_of_values[32] &gt; '0.32'::double precision)) | > | Planning time: 0.405 ms | > | Execution time: 55.013 ms | > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=3df9a94cfe2b64c8cb767e741cf4fdc8)*
back to fiddle