Extension intarray and null values
Hello,
I use tables with several "int[]" columns.
I use the "@>" operator to filter my data.
To increase speed , i install the "intarray" extension. Now queries are
very fast with the specific indexes (i use "gin__int_ops").
But, in few columns, i have null values in my intarray and i cannot use the
"@>" operator.
Because i have the error "ERROR: array must not contain nulls".
The documentation says that the standard operator "@>" is overrided by the
intarray extension when arguments are int[].
Do you have a solution, to rename the "@>(int{], int[])" to use it only
when it is possible and to prevent the override of the current behavior ?
Or if you know, how use the default @> operator instead of the override.
Thank you,
Eric.
Eric Brison <eric.brison@anakeen.com> writes:
I use tables with several "int[]" columns.
I use the "@>" operator to filter my data.
To increase speed , i install the "intarray" extension. Now queries are
very fast with the specific indexes (i use "gin__int_ops").
But, in few columns, i have null values in my intarray and i cannot use the
"@>" operator.
Do you really need to use intarray, rather than the built-in gin array
indexing features?
Yeah, the intarray implementation is probably a shade faster, but it's
faster precisely because it doesn't handle cases like null entries.
If you're in need of that, my suggestion is to ditch intarray.
regards, tom lane
yes i a have a specific query with many rows and big int array These data
not contains null values. And in this case , i use the specific GIN index.
The query time decrease from 30s to 100ms with the index.
I found that i can use the "arraycontains" builtin function for int[] with
null values. But i'm not sure that this function name is safe in all
postgresql version (>= 10)
Eric.
Le jeu. 25 févr. 2021 à 16:06, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Eric Brison <eric.brison@anakeen.com> writes:
I use tables with several "int[]" columns.
I use the "@>" operator to filter my data.
To increase speed , i install the "intarray" extension. Now queries are
very fast with the specific indexes (i use "gin__int_ops").
But, in few columns, i have null values in my intarray and i cannot usethe
"@>" operator.
Do you really need to use intarray, rather than the built-in gin array
indexing features?Yeah, the intarray implementation is probably a shade faster, but it's
faster precisely because it doesn't handle cases like null entries.
If you're in need of that, my suggestion is to ditch intarray.regards, tom lane
--
*Eric Brison - Responsable R&D*
8 av Yves Brunaud - 31770 Colomiers
T : +33 5 31 61 55 23
www.anakeen.com
Ce message, avec ses pièces jointes, est privé et peut contenir des
informations confidentielles ou privilégiées qui ne
doivent pas être diffusés, exploités ou copiés sans autorisation d'Anakeen.
Si vous avez reçu ce message par erreur, veuillez le signaler
à l'expéditeur et le détruire ainsi que les pièces jointes. Les messages
électroniques étant susceptibles d'altération, Anakeen décline toute
responsabilité si ce message a été altéré, déformé ou falsifié.
This message, with its attachments, is private and may contain confidential
or privileged information that may be protected by law; they should not be
distributed, used or copied without authorization of Anakeen. If you have
received this email in error, please notify the sender and delete this
message and its attachments. As emails may be altered, Anakeen is not
liable for messages that have been modified, changed or falsified.
Eric Brison <eric.brison@anakeen.com> writes:
yes i a have a specific query with many rows and big int array These data
not contains null values. And in this case , i use the specific GIN index.
The query time decrease from 30s to 100ms with the index.
My point is that you can also make a gin index on an integer array
using the built-in array opclass. It might be a shade slower than
intarray, but maybe it's 120ms instead of 100, and it'll handle
nulls correctly.
regards, tom lane