CODEWITHSUNDEEP
postgresqlindexingvarchar
Hemanth S. Vaddi
Hemanth S. Vaddi

Reputation: 463

Postgres - Indexing long character varying array type column using GIN?

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

Upvotes: 0

Views: 561

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247445

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).

Upvotes: 1

Related Questions