I have solved thanks to Jeff Janes on the pgsql-performance mailing list:
The GIN index was not used by PostgreSQL for the "NOT" operation. Creating a Btree index on the whole array solved the problem, allowing an index only scan. Now the query takes only a few milliseconds instead of minutes.
You've already had a good answer, so here's a bit more to file under food for thought. First, your question reminded me of an interesting-sounding technique:
I'd be interested in comments from those who have tried such a strategy.
As another thought, another option is to ...
If 't1' is a rare tag, counting rows that don't have a tag results in counting most of your "millions of rows". And even if 't1' is very common, counting more than a few percent of rows from the index is no improvement over a sequential scan. Either way, this is never going to be very fast. Indexes are not going to help.
If you have to do several counts ...
What I really want to know is do sleeping sessions hold a thread?
No, they do not,
A SQL Server worker thread, also known as worker or thread, is a
logical representation of an operating system thread. When executing
serial requests, the SQL Server Database Engine will spawn a worker to
execute the active task.
But instead of actually spawning ...