Here’s a quick one to give you a nice potential speed up your PostgreSQL Array queries.
Suppose you have a sites table with an aliases Array column and you defined
an index like this:
So far so good. Except that a (otherwise perfectly working) query like
to find out the site for a given alias name will never use that index. Instead
you have to use one of the PostgreSQL Array functions like so:
If this were a Rails app, you might query your Site model like that:
The shorter curly brackets syntax is not easily used with Rails due to Rails’
If you want to try this out on your own with a small data set, don’t forget to
enforce index usage with SET enable_seqscan TO off; in the psql shell you are using, otherwise the optimizer
will choose sequential scans over index usage due to the small table size.
With this set, have PostgreSQL explain what it does: