2

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.


1

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: https://heap.io/blog/engineering/running-10-million-postgresql-indexes-in-production I'd be interested in comments from those who have tried such a strategy. As another thought, another option is to ...


1

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


1

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


Only top voted, non community-wiki answers of a minimum length are eligible