Re: Slow "not in array" operation
| От | Marco Colli |
|---|---|
| Тема | Re: Slow "not in array" operation |
| Дата | |
| Msg-id | CAFvCgN6bQhf03jdakdk1eVMad8r=6nFV=cmy2v_sp0f8_kZ-bg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Slow "not in array" operation (Michael Lewis <mlewis@entrata.com>) |
| Ответы |
Re: Slow "not in array" operation
|
| Список | pgsql-performance |
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)
What's the plan for the slow one? What's the time to just count all rows?
В списке pgsql-performance по дате отправления: