Re: Slow "not in array" operation

To be honest, I have simplified the question above. In order to show you
the plan, I must show you the actual query, which is this:

=== QUERY ===

SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" =
123 AND "subscriptions"."trashed_at" IS NULL AND NOT (tags @>
ARRAY['en']::varchar[]);

=== QUERY PLAN ===

QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------

Finalize Aggregate (cost=2152593.04..2152593.05 rows=1 width=8) (actual
time=70555.561..70555.561 rows=1 loops=1)

-> Gather (cost=2152592.31..2152593.02 rows=7 width=8) (actual
time=70540.641..70702.365 rows=8 loops=1)

Workers Planned: 7

Workers Launched: 7

-> Partial Aggregate (cost=2151592.31..2151592.32 rows=1
width=8) (actual time=70537.376..70537.377 rows=1 loops=8)

-> Parallel Seq Scan on subscriptions
(cost=0.00..2149490.49 rows=840731 width=0) (actual time=0.742..70479.359
rows=611828 loops=8)

Filter: ((trashed_at IS NULL) AND (NOT (tags @>
'{en}'::character varying[])) AND (project_id = 123))

Rows Removed by Filter: 4572769

Planning Time: 1.304 ms

Execution Time: 70702.463 ms

(10 rows)

=== INDEXES ===

Indexes:

"subscriptions_pkey" PRIMARY KEY, btree (id)

"index_subscriptions_on_project_id_and_created_at" btree (project_id,
created_at DESC)

"index_subscriptions_on_project_id_and_tags" gin (project_id, tags)
WHERE trashed_at IS NULL

"index_subscriptions_on_project_id_and_trashed_at" btree (project_id,
trashed_at DESC)

=== NOTES ===

Running the query without the last filter on tags takes only 500ms.
Unfortunately I cannot make strict assumptions on data or tags: for example
I also have to count subscriptions in a project that don't have tag A and
don't have tag B, etc. This means that I cannot simply calculate the total
and then make a subtraction.

On Tue, Nov 12, 2019 at 7:40 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> What's the plan for the slow one? What's the time to just count all rows?
>
>>