Search similar images in PostgreSQL

with index and sorting. 1000000 count

EXPLAIN ANALYZE SELECT smlar(images.image_array, '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::int[]) as similarity FROM images WHERE images.image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::int[] ORDER BY similarity DESC; 


 Sort  (cost=4020.94..4023.41 rows=986 width=924) (actual time=2888.472..2901.977 rows=200000 loops=1)
   Sort Key: (smlar(image_array, '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::integer[]))
   Sort Method: quicksort  Memory: 15520kB
   ->  Bitmap Heap Scan on images  (cost=286.64..3971.91 rows=986 width=924) (actual time=474.436..2729.638 rows=200000 loops=1)
         Recheck Cond: (image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::integer[])
         ->  Bitmap Index Scan on image_array_gist  (cost=0.00..286.39 rows=986 width=0) (actual time=421.140..421.140 rows=200000 loops=1)
               Index Cond: (image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::integer[])
 Total runtime: 2912.207 ms
(8 rows)

with index. 1000000 count

test=# SELECT count(*) from images;
  count 
---------
 1000000
(1 row)


test=# EXPLAIN ANALYZE SELECT count(*) FROM images WHERE images.image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::int[];


 Bitmap Heap Scan on images  (cost=286.64..3969.45 rows=986 width=4) (actual time=504.312..2047.533 rows=200000 loops=1)
   Recheck Cond: (image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,10
21253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::integer[])
   ->  Bitmap Index Scan on image_array_gist  (cost=0.00..286.39 rows=986 width=0) (actual time=446.109..446.109 rows=200000 loops=1)
         Index Cond: (image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::integer[])
 Total runtime: 2152.411 ms
(5 rows)

with index. 200000 count

test=# EXPLAIN ANALYZE SELECT id FROM images WHERE images.image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::int[];



 Aggregate  (cost=815.75..815.76 rows=1 width=0) (actual time=320.428..320.428 rows=1 loops=1)
   ->  Bitmap Heap Scan on images  (cost=66.42..815.25 rows=200 width=0) (actual time=108.127..304.524 rows=40000 loops=1)
         Recheck Cond: (image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::integer[])
         ->  Bitmap Index Scan on image_array_gist  (cost=0.00..66.37 rows=200 width=0) (actual time=90.814..90.814 rows=40000 loops=1)
               Index Cond: (image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::integer[])
 Total runtime: 320.487 ms
(6 rows)

without index

test=# SELECT count(*) from images;
 count 
--------
 200000
(1 row)


test=# EXPLAIN ANALYZE SELECT id FROM images WHERE images.image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::int[];


Aggregate  (cost=14.58..14.59 rows=1 width=0) (actual time=1.785..1.785 rows=1 loops=1)
   ->  Seq Scan on images  (cost=0.00..14.50 rows=33 width=0) (actual time=0.115..1.772 rows=20 loops=1)
         Filter: (image_array % '{1010259,1011253,1012249,1013251,1014249,1015249,1016247,1017252,1018252,1019251,1020251,1021253,1022258,1023257,1024257,1110258,1111258,1112252,1113251,1114251,1115250,1116247,1117252,1118252,1119252,1120252,1121253,1122257,1123257,1124257,1210258,1211258,1212258,1213253,1214251,1215251,1216248,1217253,1218253,1219253,1220253,1221253,1222253,1223253,1224252,1310258,1311258,1312258,1313258,1314258,1315252,1316248,1317253,1318257,1319257,1320253,1321253,1322257,1323253,1324253,1410258,1411258,1412258,1413258,1414258,1415257,1416250,1417253,1418257,1419257,1420257,1421253,1422257,1423257,1424253,1510258,1511258,1512258,1513258,1514258,1515257,1516251,1517253,1518257,1519253,1520252,1521252,1522252,1523251,1524250,1610258,1611258,1612258,1613258,1614258,1615257,1616252,1617252,1618251,1619250,1620247,1621251,1622251,1623250,1624250,1710258,1711258,1712258,1713258,1714258,1715257,1716252,1717257,1718257,1719257,1720252,1721253,1722252,1723253,1724253,1810258,1811258,1812258,1813258,1814258,1815258,1816252,1817257,1818257,1819252,1820245,1821100,1822249,1823253,1824257,1910258,1911258,1912258,1913258,1914259,1915258,1916252,1917257,1918257,1919252,1920235,1921222,1922217,1923251,1924253,2010258,2011258,2012258,2013258,2014258,2015253,2016251,2017257,2018257,2019252,2020064,2021248,2022250,2023248,2024252,2110258,2111258,2112258,2113259,2114253,2115251,2116250,2117253,2118257,2119257,2120251,2121249,2122251,2123249,2124234,2210258,2211258,2212259,2213258,2214252,2215251,2216249,2217253,2218257,2219257,2220257,2221253,2222253,2223253,2224251,2310258,2311258,2312258,2313257,2314251,2315250,2316246,2317253,2318257,2319253,2320253,2321253,2322253,2323253,2324253,2410258,2411258,2412257,2413252,2414251,2415249,2416246,2417252,2418257,2419253,2420253,2421253,2422253,2423253,2424252}'::integer[])
 Total runtime: 5152.819 ms
(4 rows)