Slow "not in array" operation

From: Marco Colli <collimarco91(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Slow "not in array" operation
Date: 2019-11-12 17:29:31
Message-ID: CAFvCgN73NJbSdtCFvcD3=Hh+xGipDkUk+H4uzubQXx2rncQJ+w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have a large table with millions of rows. Each row has an array field
"tags". I also have the proper GIN index on tags.

Counting the rows that have a tag is fast (~7s):
SELECT COUNT(*) FROM "subscriptions" WHERE (tags @> ARRAY['t1']::varchar[]);

However counting the rows that don't have a tag is extremely slow (~70s):
SELECT COUNT(*) FROM "subscriptions" WHERE NOT (tags @>
ARRAY['t1']::varchar[]);

I have also tried other variants, but with the same results (~70s):
SELECT COUNT(*) FROM "subscriptions" WHERE NOT ('t1' = ANY (tags));

How can I make the "not in array" operation fast?

Any help would be appreciated, thank you!
Marco Colli

PostgreSQL 11 on Ubuntu 18LTS

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Michael Lewis 2019-11-12 18:39:46 Re: Slow "not in array" operation
Previous Message Tomas Vondra 2019-11-07 00:45:51 Re: FPGA optimization ...