科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>sql - Why subquery if much faster than exists? - Stack Overflow

    sql - Why subquery if much faster than exists? - Stack Overflow

    programmeradmin0浏览0评论

    I have 2 indexes:

    CREATE INDEX ix_id ON ONLY public.push_webhooks USING btree (id)
CREATE INDEX idx_push_webhooks_campaign_sending ON ONLY public.push_webhooks USING btree (campaign_id, sending_id)

    This query:

    SELECT 
    w.id
FROM public.push_webhooks w
INNER JOIN public.push_billings_campaign_info i ON i.campaign_id = w.campaign_id
INNER JOIN public.contacts_1 c ON c.id = w.contact_id 
WHERE
    (SELECT registration_id FROM push_webhooks w2 WHERE w2.campaign_id = w.campaign_id AND w2.sending_id = w.sending_id AND w2.registration_id IS NOT NULL LIMIT 1) IS NOT null
LIMIT 1000

    executes in 5 miliseconds, but this one:

    SELECT 
    w.id
FROM public.push_webhooks w
INNER JOIN public.push_billings_campaign_info i ON i.campaign_id = w.campaign_id
INNER JOIN public.contacts_1 c ON c.id = w.contact_id 
WHERE
    EXISTS (SELECT 1 FROM push_webhooks w2 WHERE w2.campaign_id = w.campaign_id AND w2.sending_id = w.sending_id AND w2.registration_id IS NOT NULL)
LIMIT 1000

    needs 300 ms to finish. Why there is such a difference? Execution plan of a first query:

    Limit  (cost=0.72..776.33 rows=1000 width=16) (actual time=0.040..6.590 rows=1000 loops=1)
  ->  Nested Loop  (cost=0.72..771777.05 rows=995056 width=16) (actual time=0.040..6.493 rows=1000 loops=1)
        ->  Nested Loop  (cost=0.28..281399.45 rows=995056 width=20) (actual time=0.032..4.393 rows=1000 loops=1)
              ->  Append  (cost=0.00..256557.84 rows=995056 width=24) (actual time=0.022..4.015 rows=1000 loops=1)
                    ->  Seq Scan on push_webhooks_p20250125 w_1  (cost=0.00..0.00 rows=1 width=24) (actual time=0.002..0.004 rows=0 loops=1)
                          Filter: ((SubPlan 1) IS NOT NULL)
                          SubPlan 1
                            ->  Limit  (cost=0.00..0.22 rows=1 width=404) (actual time=0.003..0.003 rows=1 loops=1000)
                                  ->  Append  (cost=0.00..3.74 rows=17 width=404) (actual time=0.003..0.003 rows=1 loops=1000)
                                        ->  Seq Scan on push_webhooks_p20250125 w2_1  (cost=0.00..0.00 rows=1 width=516) (actual time=0.000..0.000 rows=0 loops=1000)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250126 w2_2  (cost=0.00..0.00 rows=1 width=516) (actual time=0.000..0.000 rows=0 loops=1000)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Index Scan using push_webhooks_p20250127_campaign_id_sending_id_idx on push_webhooks_p20250127 w2_3  (cost=0.42..2.64 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=1000)
                                              Index Cond: ((campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                              Filter: (registration_id IS NOT NULL)
                                        ->  Seq Scan on push_webhooks_p20250128 w2_4  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250129 w2_5  (cost=0.00..1.01 rows=1 width=5) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250130 w2_6  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250131 w2_7  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250201 w2_8  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250202 w2_9  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250203 w2_10  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250204 w2_11  (cost=0.00..0.00 rows=1 width=5) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250205 w2_12  (cost=0.00..0.00 rows=1 width=153) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250206 w2_13  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250207 w2_14  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250208 w2_15  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250209 w2_16  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_default w2_17  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                    ->  Seq Scan on push_webhooks_p20250126 w_2  (cost=0.00..0.00 rows=1 width=24) (actual time=0.000..0.001 rows=0 loops=1)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250127 w_3  (cost=0.00..251581.33 rows=995040 width=24) (actual time=0.019..3.898 rows=1000 loops=1)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250128 w_4  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250129 w_5  (cost=0.00..1.23 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250130 w_6  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250131 w_7  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250201 w_8  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250202 w_9  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250203 w_10  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250204 w_11  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250205 w_12  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250206 w_13  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250207 w_14  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250208 w_15  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250209 w_16  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_default w_17  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
              ->  Memoize  (cost=0.28..0.30 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1000)
                    Cache Key: w.campaign_id
                    Cache Mode: logical
                    Hits: 999  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                    ->  Index Only Scan using pk_push_billings_campaign_info on push_billings_campaign_info i  (cost=0.27..0.29 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)
                          Index Cond: (campaign_id = w.campaign_id)
                          Heap Fetches: 1
        ->  Index Only Scan using ix_contacts_1_id on contacts_1 c  (cost=0.43..0.48 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1000)
              Index Cond: (id = w.contact_id)
              Heap Fetches: 0
Planning Time: 0.945 ms
Execution Time: 6.762 ms

    and second query:

    Limit  (cost=30539.03..42411.58 rows=1000 width=16) (actual time=127.413..140.200 rows=1000 loops=1)
  ->  Gather  (cost=30539.03..58807.57 rows=2381 width=16) (actual time=127.411..140.140 rows=1000 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Nested Loop  (cost=29539.03..57569.47 rows=595 width=16) (actual time=111.024..112.297 rows=203 loops=5)
              ->  Parallel Hash Semi Join  (cost=29538.59..57276.34 rows=595 width=20) (actual time=110.977..111.595 rows=203 loops=5)
                    Hash Cond: ((i.campaign_id = w2.campaign_id) AND (w.sending_id = w2.sending_id))
                    ->  Hash Join  (cost=33.45..26451.34 rows=250014 width=32) (actual time=0.235..0.355 rows=203 loops=5)
                          Hash Cond: (w.campaign_id = i.campaign_id)
                          ->  Parallel Append  (cost=0.00..25754.99 rows=250010 width=28) (actual time=0.015..0.100 rows=203 loops=5)
                                ->  Parallel Seq Scan on push_webhooks_p20250127 w_3  (cost=0.00..24503.94 rows=322594 width=28) (actual time=0.009..0.077 rows=203 loops=5)
                                ->  Parallel Seq Scan on push_webhooks_p20250129 w_5  (cost=0.00..1.01 rows=1 width=28) (actual time=0.007..0.007 rows=0 loops=3)
                                ->  Parallel Seq Scan on push_webhooks_p20250125 w_1  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250126 w_2  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250128 w_4  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250130 w_6  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250131 w_7  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250201 w_8  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250202 w_9  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250203 w_10  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=2)
                                ->  Parallel Seq Scan on push_webhooks_p20250204 w_11  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250205 w_12  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250206 w_13  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250207 w_14  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250208 w_15  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250209 w_16  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_default w_17  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                          ->  Hash  (cost=28.20..28.20 rows=420 width=4) (actual time=0.201..0.202 rows=420 loops=5)
                                Buckets: 1024  Batches: 1  Memory Usage: 23kB
                                ->  Seq Scan on push_billings_campaign_info i  (cost=0.00..28.20 rows=420 width=4) (actual time=0.018..0.151 rows=420 loops=5)
                    ->  Parallel Hash  (cost=25754.99..25754.99 rows=250010 width=8) (actual time=109.562..109.565 rows=200008 loops=5)
                          Buckets: 1048576  Batches: 1  Memory Usage: 0kB
                          ->  Parallel Append  (cost=0.00..25754.99 rows=250010 width=8) (actual time=0.021..63.289 rows=200008 loops=5)
                                ->  Parallel Seq Scan on push_webhooks_p20250127 w2_3  (cost=0.00..24503.94 rows=322594 width=8) (actual time=0.018..52.370 rows=200008 loops=5)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250129 w2_5  (cost=0.00..1.01 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250125 w2_1  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.001 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250126 w2_2  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250128 w2_4  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250130 w2_6  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.001 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250131 w2_7  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250201 w2_8  (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250202 w2_9  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250203 w2_10  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250204 w2_11  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250205 w2_12  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250206 w2_13  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250207 w2_14  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250208 w2_15  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250209 w2_16  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_default w2_17  (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
              ->  Index Only Scan using ix_contacts_1_id on contacts_1 c  (cost=0.43..0.48 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1014)
                    Index Cond: (id = w.contact_id)
                    Heap Fetches: 0
Planning Time: 1.608 ms
Execution Time: 140.358 ms

    I have 2 indexes:

    CREATE INDEX ix_id ON ONLY public.push_webhooks USING btree (id)
CREATE INDEX idx_push_webhooks_campaign_sending ON ONLY public.push_webhooks USING btree (campaign_id, sending_id)

    This query:

    SELECT 
    w.id
FROM public.push_webhooks w
INNER JOIN public.push_billings_campaign_info i ON i.campaign_id = w.campaign_id
INNER JOIN public.contacts_1 c ON c.id = w.contact_id 
WHERE
    (SELECT registration_id FROM push_webhooks w2 WHERE w2.campaign_id = w.campaign_id AND w2.sending_id = w.sending_id AND w2.registration_id IS NOT NULL LIMIT 1) IS NOT null
LIMIT 1000

    executes in 5 miliseconds, but this one:

    SELECT 
    w.id
FROM public.push_webhooks w
INNER JOIN public.push_billings_campaign_info i ON i.campaign_id = w.campaign_id
INNER JOIN public.contacts_1 c ON c.id = w.contact_id 
WHERE
    EXISTS (SELECT 1 FROM push_webhooks w2 WHERE w2.campaign_id = w.campaign_id AND w2.sending_id = w.sending_id AND w2.registration_id IS NOT NULL)
LIMIT 1000

    needs 300 ms to finish. Why there is such a difference? Execution plan of a first query:

    Limit  (cost=0.72..776.33 rows=1000 width=16) (actual time=0.040..6.590 rows=1000 loops=1)
  ->  Nested Loop  (cost=0.72..771777.05 rows=995056 width=16) (actual time=0.040..6.493 rows=1000 loops=1)
        ->  Nested Loop  (cost=0.28..281399.45 rows=995056 width=20) (actual time=0.032..4.393 rows=1000 loops=1)
              ->  Append  (cost=0.00..256557.84 rows=995056 width=24) (actual time=0.022..4.015 rows=1000 loops=1)
                    ->  Seq Scan on push_webhooks_p20250125 w_1  (cost=0.00..0.00 rows=1 width=24) (actual time=0.002..0.004 rows=0 loops=1)
                          Filter: ((SubPlan 1) IS NOT NULL)
                          SubPlan 1
                            ->  Limit  (cost=0.00..0.22 rows=1 width=404) (actual time=0.003..0.003 rows=1 loops=1000)
                                  ->  Append  (cost=0.00..3.74 rows=17 width=404) (actual time=0.003..0.003 rows=1 loops=1000)
                                        ->  Seq Scan on push_webhooks_p20250125 w2_1  (cost=0.00..0.00 rows=1 width=516) (actual time=0.000..0.000 rows=0 loops=1000)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250126 w2_2  (cost=0.00..0.00 rows=1 width=516) (actual time=0.000..0.000 rows=0 loops=1000)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Index Scan using push_webhooks_p20250127_campaign_id_sending_id_idx on push_webhooks_p20250127 w2_3  (cost=0.42..2.64 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=1000)
                                              Index Cond: ((campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                              Filter: (registration_id IS NOT NULL)
                                        ->  Seq Scan on push_webhooks_p20250128 w2_4  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250129 w2_5  (cost=0.00..1.01 rows=1 width=5) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250130 w2_6  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250131 w2_7  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250201 w2_8  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250202 w2_9  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250203 w2_10  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250204 w2_11  (cost=0.00..0.00 rows=1 width=5) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250205 w2_12  (cost=0.00..0.00 rows=1 width=153) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250206 w2_13  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250207 w2_14  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250208 w2_15  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_p20250209 w2_16  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                                        ->  Seq Scan on push_webhooks_default w2_17  (cost=0.00..0.00 rows=1 width=516) (never executed)
                                              Filter: ((registration_id IS NOT NULL) AND (campaign_id = w_1.campaign_id) AND (sending_id = w_1.sending_id))
                    ->  Seq Scan on push_webhooks_p20250126 w_2  (cost=0.00..0.00 rows=1 width=24) (actual time=0.000..0.001 rows=0 loops=1)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250127 w_3  (cost=0.00..251581.33 rows=995040 width=24) (actual time=0.019..3.898 rows=1000 loops=1)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250128 w_4  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250129 w_5  (cost=0.00..1.23 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250130 w_6  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250131 w_7  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250201 w_8  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250202 w_9  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250203 w_10  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250204 w_11  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250205 w_12  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250206 w_13  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250207 w_14  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250208 w_15  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_p20250209 w_16  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
                    ->  Seq Scan on push_webhooks_default w_17  (cost=0.00..0.00 rows=1 width=24) (never executed)
                          Filter: ((SubPlan 1) IS NOT NULL)
              ->  Memoize  (cost=0.28..0.30 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1000)
                    Cache Key: w.campaign_id
                    Cache Mode: logical
                    Hits: 999  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                    ->  Index Only Scan using pk_push_billings_campaign_info on push_billings_campaign_info i  (cost=0.27..0.29 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)
                          Index Cond: (campaign_id = w.campaign_id)
                          Heap Fetches: 1
        ->  Index Only Scan using ix_contacts_1_id on contacts_1 c  (cost=0.43..0.48 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1000)
              Index Cond: (id = w.contact_id)
              Heap Fetches: 0
Planning Time: 0.945 ms
Execution Time: 6.762 ms

    and second query:

    Limit  (cost=30539.03..42411.58 rows=1000 width=16) (actual time=127.413..140.200 rows=1000 loops=1)
  ->  Gather  (cost=30539.03..58807.57 rows=2381 width=16) (actual time=127.411..140.140 rows=1000 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Nested Loop  (cost=29539.03..57569.47 rows=595 width=16) (actual time=111.024..112.297 rows=203 loops=5)
              ->  Parallel Hash Semi Join  (cost=29538.59..57276.34 rows=595 width=20) (actual time=110.977..111.595 rows=203 loops=5)
                    Hash Cond: ((i.campaign_id = w2.campaign_id) AND (w.sending_id = w2.sending_id))
                    ->  Hash Join  (cost=33.45..26451.34 rows=250014 width=32) (actual time=0.235..0.355 rows=203 loops=5)
                          Hash Cond: (w.campaign_id = i.campaign_id)
                          ->  Parallel Append  (cost=0.00..25754.99 rows=250010 width=28) (actual time=0.015..0.100 rows=203 loops=5)
                                ->  Parallel Seq Scan on push_webhooks_p20250127 w_3  (cost=0.00..24503.94 rows=322594 width=28) (actual time=0.009..0.077 rows=203 loops=5)
                                ->  Parallel Seq Scan on push_webhooks_p20250129 w_5  (cost=0.00..1.01 rows=1 width=28) (actual time=0.007..0.007 rows=0 loops=3)
                                ->  Parallel Seq Scan on push_webhooks_p20250125 w_1  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250126 w_2  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250128 w_4  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250130 w_6  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250131 w_7  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250201 w_8  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250202 w_9  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250203 w_10  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=2)
                                ->  Parallel Seq Scan on push_webhooks_p20250204 w_11  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250205 w_12  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250206 w_13  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250207 w_14  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250208 w_15  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_p20250209 w_16  (cost=0.00..0.00 rows=1 width=28) (actual time=0.000..0.001 rows=0 loops=1)
                                ->  Parallel Seq Scan on push_webhooks_default w_17  (cost=0.00..0.00 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)
                          ->  Hash  (cost=28.20..28.20 rows=420 width=4) (actual time=0.201..0.202 rows=420 loops=5)
                                Buckets: 1024  Batches: 1  Memory Usage: 23kB
                                ->  Seq Scan on push_billings_campaign_info i  (cost=0.00..28.20 rows=420 width=4) (actual time=0.018..0.151 rows=420 loops=5)
                    ->  Parallel Hash  (cost=25754.99..25754.99 rows=250010 width=8) (actual time=109.562..109.565 rows=200008 loops=5)
                          Buckets: 1048576  Batches: 1  Memory Usage: 0kB
                          ->  Parallel Append  (cost=0.00..25754.99 rows=250010 width=8) (actual time=0.021..63.289 rows=200008 loops=5)
                                ->  Parallel Seq Scan on push_webhooks_p20250127 w2_3  (cost=0.00..24503.94 rows=322594 width=8) (actual time=0.018..52.370 rows=200008 loops=5)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250129 w2_5  (cost=0.00..1.01 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250125 w2_1  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.001 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250126 w2_2  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250128 w2_4  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250130 w2_6  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.001 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250131 w2_7  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250201 w2_8  (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250202 w2_9  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250203 w2_10  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250204 w2_11  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250205 w2_12  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250206 w2_13  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250207 w2_14  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250208 w2_15  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_p20250209 w2_16  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
                                ->  Parallel Seq Scan on push_webhooks_default w2_17  (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
                                      Filter: (registration_id IS NOT NULL)
              ->  Index Only Scan using ix_contacts_1_id on contacts_1 c  (cost=0.43..0.48 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1014)
                    Index Cond: (id = w.contact_id)
                    Heap Fetches: 0
Planning Time: 1.608 ms
Execution Time: 140.358 ms
    • sql
    • postgresql
    Share Improve this question asked Feb 6 at 18:16 dafiedafie 1,16910 silver badges28 bronze badges 3
    • Please show the full table and index definitions for all relevant tables. – Charlieface Commented Feb 6 at 18:44
    • 1 Looks like you can improve on both of these versions with a window function SELECT ... FROM (SELECT *, COUNT(registration_id) OVER (PARTITION BY campaign_id, sending_id) AS count_id FROM public.push_webhooks w) w INNER JOIN ... WHERE count_id > 0 – Charlieface Commented Feb 6 at 18:53
    • 1 It can be made even simpler by replacing the entire WHERE with WHERE w.registration_id IS NOT NULL GROUP BY w.id. The query should also have an ORDER BY w.id TO accompany the LIMIT 1000 so that the returned set is determimistic. Using GROUP BY w.id also eliminates redundant occurrences of id that might otherwise result from joining to the other tables. – JohnH Commented Feb 6 at 21:22
    Add a comment  | 

    2 Answers 2

    Reset to default 1

    If you want exists to work like subquery, use Limit on the exists query, because the exists scans the entire table and then check if a match exists. If you have good indexes in case the table was large then both will work fine, if not both then both will work slowly.

    Try with those indexes :

    CREATE INDEX X001 ON public.push_webhooks (campaign_id, sending_id, registration_id);
CREATE INDEX X002 ON public.push_webhooks (campaign_id, contact_id, sending_id) INCLUDE (id);

CREATE INDEX X003 ON public.push_billings_campaign_info (campaign_id);

CREATE INDEX X004 ON public.contacts_1 (contact_id);

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论