0

I'm trying to add index to a table with column of character varying array type.

CREATE TABLE test1 (abc character varying(5000)[]);
INSERT INTO test1 VALUES ('{"ok"}');
INSERT INTO test1 VALUES ('{"E7j0JY8vBgNzdzXHI8gvJ5eWe0HphmiOXhkazbogMrhZmZa84sblywEWVVMGD3jjwq3Etcy87ZITCrIJWyLAIBfynHCavdzpfTutGXmbc9JkuZEX8YuUbjeJc2TvktyNZJXxFlh1GaogibFkxd0qDNM07qk05QuCeX1UudrsFnLLZXc0s4JeP6heekAlJQBsKt2NSM0hcLHQyYq61iofXraGOV7bnPrcfeP6fEq2LDrAb9QVzzJ0MdYduiFxUfmoSoBs9HjqN5ofUMD8B6qi81cGdMTxYN359n6yf65htfIywSPPvRDbTBpgfdejuTtyfXoEY9awt2zzlkdJDXvMm1FoaEz4hHtmsg92C91UR2yE9z37Ws0yaNWCI0KPxGVEAMuzwh6JGDljgLXNo9MZTBmJd24Vhr4HR9YDHazpaWXje9ImUMiurRRKjcUcP5hZIWWl2kKpcfxUMrQsTnqgcTIj7WymW62DgqnIzHCFrXc6ykthIieynqtevghDZNVPiuWtDoquE0dzrDIFsdYUm74hFeTB5NdIsk0dZBjwmKBour5qJiVMJPz33AbZEPhrI1JbBNhiovrTh2Q5tz6PNv9BExM9zsHB19uU8AtHhlYF3XMwSHjKzMgYobohSl3hfpFuDoqKXhgnuR9Ni4bvoyUcIyyp9rnhs1yawm1r4922Ule3BAoDDBvVeREcvRUT2u2j1eKWmTiXQzx03XdukpgLc0rZ1DKB7V7US403XXVGiyv6QEpPeMTlkPdsSwqA4BmW73qQlsXqVg2COzVE8uAGjXqYZxLsiyndTum3b3VXCkXcogp0fJNGi47M2xkW6novZ1ZfCqL0pMspgAiDLSKHzu8GFvzs3wi3FJLKJrmmLp201U4zXhsF3K2TPw0QrTto3ArUzijt0Ea8pi2LzB9pw9ZpTmc2yUvaL2q8sdeF8TIwPptR0SxOL4NTxNij7oieCpT74NaMXUqkwbKHO59TeGNd6hRlp2sj6JRicibtNBN1WQYWiivMfXDylt9hLr0Cgw2V9b586PXf8noLH62SOE97ua9axU2mhmznhELWDmnISTYoNVsoLcHOGKK2EFJKovEc5rtQghHAti3E4ha5PftWRCMleindEPEJAjokXDkIQxx9Q8AcpZrJw389eag85ClGQnu0jhyn0aRcIe1f9wAK5wUfvzAD39WGUewzDogmPGgbRU9UtR7aRd2v09idFzmPFPxRUgeSQlz7uEwYzsLzqcuahIKH5kcc6B3OAOWv5c5bS7MO74ko7ToUvtdHr6Ah0C3DeN9Br4QLOjSKkQKVhWw66JGGJQvjKIhzntDFLXsSuN9iD32h3q62DW88RFcdx2MjL8SXKeZm11NkAoRERuQBvXLOWo9JA1tq8J2CZE95vXlFuvge0xxiLS7vB0GMtYzWvMgH3C9qshNMQfeAl4Bm8pgB3M1SjxBz0CgCXhy69482BQFTRCBBVwzrc5VLmOXZEA4iKuMXxkUGKkB8PzXjspeo3VyIWjDZGpbDBvfdDViCGo8OOHU43T81vSeRHaN3U4JYc3UW0mPEwF2HOY4hKbgbELKIMsg9HbwPqcv3SmCIuVqORSrnq0sysJL7TwZt8mheSZ4nSNIXDOBLdO6V29X3XFVjRJIK0bdLfxs59E0SiWwGGqGEI0MtcCbbSM5QfzQTALG7E0Mbf2pSEbw7ZwXKLh4Il3mLaqTmtJt3p18wTJOfCi7Qkwem66emBjx5YAixDhp6D73e8X9mTNZvClkS9COrAgqcj9XwnyDlaw1Vgg7UJk6uaOXgW57LJIqkAruI3lrZEOfYe8gYgTaUUDzXHPKLj5BzIqQpU8agA5m11NiDvdocr9yrsJLdfNMWyjTeXmq61mi9Ok31sWUxmuRlolfOMtjm1ev3yJzaTdrq3Xekedtmn889KVCw2AvV8KvT15h2bQOtqTPQRjNQGVyRspE9j5tPupqR98YrobNXrcZE9Wifd5JsFX1vlgIgJxISQMrOZx42Win944RK0IYBKo64cyfugH6hjL0QUCLTJqio9P3kB56H10iTi8iizoJ5qZZa8jt9qAJBqmSMeZn1yw5eVbQluWKxWUyXw1iOvm8SqfWZ2UWr5G0X2XNXj8HTBH0u25T9yUsCPoIxfJ63sBoOYW3DHzOKex4DUR9I7wGyT75kRkRO3Qmqzxa3aonD5CYe3cfn9f6DRiFlinoaVXHFiOKOP81NrUei9uKzmpOfBv6ats3hOacP9wdBKVfw1ZOpCGsDDhoX2I3wn9wREojlMz9fDXr9HHYoSfVH9xDkbBsTvqGAaOsYlNQEvDno9pkWm1SI6KxkdyBAHEXFyN3272kgFFFUPEDmXRfaFvy3FB3reqaBwAZeM0h6b9Wm47jXFKil0AaRhUTf0pactOHd98doNhe4m5HCdPJQ5bvSS8c46gn0vRjAwLMzZ9Q0w9OEpTHexy5R1cxSs27pAN8LSNjt0DcGM1hbJ08wnMfn7zCmhIsqlq1F0mwc39Ndf2cJdHgpLr24Q7OxAlbCjbnM3Uoiu4GeWVFEhaUDkwXKyC0zAoggFIe8QQtpvD8eWpvw8NxkkB70H65N5xNmgKaabbF2fytiyV0geWleopRW2kohncl8wxkneVuNeJulYuKQLH6oGHmcBRA1t8fRjBfcYofxO50Gicx0NGEKHozDOD3lubc7VdjAsSne5W4zYMu8UUm6dxbqFW7QY1S9EKuhHsdXm0S6iNLaE8Zceg33u8JYSBRtvFRSYVeHHYQTBhaKZOsiLgyDWiu8JgQ0yPfkZlLQA5oQVhOrJ4ZBfO7SUTEPsAvHSlgWph4sBrSw4rRUjYFuCZvyniFJOyvS165ogqqpuVHj1LZ9ATlT7aFN0ujmzCuX3gLZdCKwWEGpZA59auM1HxcY9Q0Mn5SSh7HDw0lZaSpRF3VCkehAGjhxYEs3CgihRiIWtZfjxKFLHMGTY7bfY2vpE65xTgTsTR86dJdvDy6sCughSMkcL2pZfvEg6JGrdBw88YPoEtgzCBjAYMXC7L09ZLVKen4TdbnV0qAKnFZRkvPhlMFW4tLVx0b1LPR4xACWLYtU74BDMVGt0BYx2XzA4Xf4CMcjK6s6BdjdTI4kMV04i13lQG3ffuFey1GNqvPLaiYh1Iay9N5beCPlyVmQHlXWd9UEMMrtJ66JKW07dn3O8jfGPd4JNv1V7YU3qHD54Xm60JNbC1nhOCX5xC9L0t2gVJ3RGH0wlmVQk1hrb1mIfdXAuI2qKOu04HAv1XWOCWYyrhcacuzLyGjDJlfUcPe9qOeMXVE6p56drl0EEWxQL7vU9KcwBR6YNoCEChHgoratnDibiFDp9hlDmaif2Jm0ruww3J5cTc1EOe59VCeqFvccdbH1CzGSoJkPFwlxJxiMU2kVGjeSGdCDhS5SOGutZlfmlhnKxMmhmPGNe2OMjYfNrJlVjUEyuZw4PhZNUuqYDLxfOt6teVzVx3OeylQd7mnvSFm27Yn03Dwv3v7FU3z8s7ZqudUtNj2DxHNcsgg2xnvnGP5YD7FjYx64O4nnPqP6jhRMnXO1mJpXzdcy8rvWEnbIIVdfVXKGorhVCDugSOipMnmpEqiLtvBnMSrwhe8sU3MGdJOy4PA5KEcyLSdeCCbBbTiNt3kOMOSaiByEUhDsyyhoo2w2aEV3QWAiJn2P8NdZ5JWZ1lWgslwL9NSpK8jADyl5u4tOoyADKohVKCadsiRe8weAKd2fvgrye10PTbI1hOkWhABqFsG6Cgx9OFWOh7dBITo1I5v1YQnKyUC3aeNqx0uxGjbe5KWsB3LPqN8O7SkiJ5XEQbVZBD2htvOis386BoUuCfyvRYweyXrwOdWoHfWXDPDx6F5oBaNvteWeqgjkLpAvdS34Idk7fMPgTMwchXp8ekyJxG6JTtKLA4zDhn71TRAo4YSPtcd5SrsytkBUyf40YIIajYGFYLKtdGGevrdwZDH0QmykUw88GApWqwwiGr3ygvtAxpyVL9Jy3nqcgF8QOSzEI1YcD5cX1j1MncKouCfuipcfxmnOZQR3Wwzn9YKQXv6OuKKKDRSfJnndkG6ivEFZ6Vfd7smewTfnZccxdYkdaEedCc8wWU4NipJadMsDi9BCGlpuzCu3t4M6PNBndUHUuWmNfAo2OEIXjtNxNrEenIuttvWDwF3kRDT03rymsi1yUsQuk9v4VpDLDjadb6eXIUS9W5vL3yVRUJNX8hp5HRD4PQbFoHOQVFJa31Nj3dlI4yvzcPSlP7DEQLqtP2nKWCYEbqBwJFytiuQqdE8sXtUc9p8pwXeFQSk2fIzXMocmCLesUH5TgphOJEcAN6sQyH2v7Pvtp9iMKPSFzGytFOSpQrofrLGJyDIwXlNHMCBa9AYIgqguFOAh9ltT5VJPCFTb7Po9LwTM4uDKZuLsMLxmohjeVV0Txv6aDhETnrfQalYgk4BlAjLFoZJhyoniM0dIyJdW8lB2kshuwYEguI2iggdv65X8DXxNyrUAIsYmYXWjNeXFyyLP4cfLIvK8C8t994LerlvhqwgwriHFmII4nAR5sdfWiRqdfjPFR7oQxOB0dOtWfC3lSomvgo4INgs6Lneg5QbiFfVXVKZ8PYhQ0Y6ynazBbNN6LEOvY2BgquHgSM59ebsVwnqDcZChn5N3oOYcqHC9TvXQfhQ8DJSS9n9b5RCduWIn42Uxy4eHSEpYGbepMKcyGVMEb9O94AxkdL1K81QLwNkw1Yt3xftOr93K4YTl2OhP04COcl3HSHDe7aOnA04MWsFgNSnUKR8I16KgwkVUsvgQJe6ROHfNFJtSYvPeqTtWkr7RDHQPaEeIPCMUVo4pMxhTMAz5J5vEQwwNDZ0qaVlPCRVF7tDXZJThAro9rGZyzdWc5ctk5E4PQr2Z7Oq3hiHLiuoxrpSZ7qrRX6TCyLJyrMUB0vQ3MpLoQ5tJ5GQ6lQ7Rrjsfhpuyc94yKu2kO6FdgoWqVu39sRq2XgxMTcGohRF9", "alpha", "VWyRaMaUucKNnmadMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhUvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaMnhpAOvLlJvorfNFAqCwQLsiKbSmxgpJhvoRCaOINFHyKgeTsqBAPUvHmMkrRTIdEhRVWyRMaUucKNnmdMXZgtJppEceutFgsoLOFStAjnQHOuBZXixVQEObHndzfsGFqBjeVkFFtwmfFIQgYMNaM"}');

create index idx_test1 on test1 using GIN(abc);

This throws an error message to create an index.

ERROR:  index row size 5016 exceeds maximum 2712 for index "idx_test1"

Is there a way to increase this max size? Or is there an alternative way to add index to such columns.

Also, is it wise to add an index to such long varchar array columns?

Note: I'm using PostgreSQL 10.7

3
  • What kind of queries are you trying to support with the index? Commented Dec 15, 2022 at 10:01
  • @a_horse_with_no_name searching in the list and also probably to do search for a specific pattern or keywords in the array. Commented Dec 15, 2022 at 10:21
  • You cannot search for patterns in an array. Bad data model. Commented Dec 15, 2022 at 11:54

1 Answer 1

1

You could create a helper function like this:

CREATE FUNCTION hash_array(text[]) RETURNS integer[]
   LANGUAGE sql IMMUTABLE AS
$$SELECT array_agg(hashtext(u.e)) FROM unnest($1) AS u(e)$$;

Then you can index an array of the hashes of the text strings:

CREATE INDEX idx_test1 ON test1 USING gin (hash_array(abc::text[]));

Now you can search like

SELECT ... FROM test1
WHERE hash_array(abc::text[]) @> ARRAY[hashtext('ok')]
  AND abc::text[] @> ARRAY['ok'];

The first condition can use the index, and the second condition removes any false positives from hash collisions.

I recommend that you use text rather than character varying(5000).

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.