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 <mle...@entrata.com> wrote:
> What's the plan for the slow one? What's the time to just count all rows?
>
>>
