Postgres Pro
Downloads
Home   >   mailing lists

Re: Slow "not in array" operation - Mailing list pgsql-performance

From Jeff Janes
Subject Re: Slow "not in array" operation
Date
Msg-id CAMkU=1ykAFLXoukwGV3dhYErQV+QnZA9EURtzG8Y=iLjarODMg@mail.gmail.com
Whole thread Raw
In response to Re: Slow "not in array" operation  (Marco Colli <collimarco91@gmail.com>)
Responses Re: Slow "not in array" operation  (Marco Colli <collimarco91@gmail.com>)
List pgsql-performance
Tree view

3) Here's the query plan that I get after disabling the seq scan:

                                                                                        QUERY PLAN                                                                                         

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

 Finalize Aggregate  (cost=2183938.89..2183938.90 rows=1 width=8) (actual time=94972.253..94972.254 rows=1 loops=1)

   ->  Gather  (cost=2183938.16..2183938.87 rows=7 width=8) (actual time=94952.895..95132.626 rows=8 loops=1)

         Workers Planned: 7

         Workers Launched: 7

         ->  Partial Aggregate  (cost=2182938.16..2182938.17 rows=1 width=8) (actual time=94950.958..94950.958 rows=1 loops=8)

               ->  Parallel Bitmap Heap Scan on subscriptions  (cost=50294.50..2180801.47 rows=854677 width=0) (actual time=1831.342..94895.208 rows=611828 loops=8)

                     Recheck Cond: ((project_id = 123) AND (trashed_at IS NULL))

                     Rows Removed by Index Recheck: 2217924

                     Filter: (NOT (tags @> '{en}'::character varying[]))

                     Rows Removed by Filter: 288545

                     Heap Blocks: exact=120301 lossy=134269

                     ->  Bitmap Index Scan on index_subscriptions_on_project_id_and_tags  (cost=0.00..48798.81 rows=6518094 width=0) (actual time=1493.823..1493.823 rows=7203173 loops=1)

                           Index Cond: (project_id = 123)

 Planning Time: 1.273 ms

 Execution Time: 95132.766 ms

(15 rows)


What was the plan for the one that took 500ms?  I don't see how it is possible that this one is 180 times slower than that one.  Maybe a hot cache versus cold cache?  Also, it seems weird to me that "trashed_at IS NULL" shows up in the recheck but not in the original  Index Cond.  Increasing work_mem can also help, but since the  Bitmap Index Scan itself took half the time there is only so much it can do.

Cheers,

Jeff

pgsql-performance by date:

Previous
From: Marco Colli
Date:
Subject: Re: Slow "not in array" operation
Next
From: Marco Colli
Date:
Subject: Re: Slow "not in array" operation