3

I have quite a complex query in PostgreSQL 10.4 on Amazon RDS (3 joins, quite a few conditions). It takes from 1.5 to 6 seconds to perform a select. However, when I want to create a materialized view from it to speed it up, it takes forever. By forever I mean more that 30 minutes (usually after this client drops connection or something).

Now, I understand that SELECTs make extensive use of parallelization to speed things up and when there is a write (as in creating a materialized view) it cannot use it, but should the results be that different (couple of seconds vs >30 minutes)?

Here's the query in question:

SELECT 
  [...]
FROM 
  "profiles" 
  LEFT OUTER JOIN (
    SELECT DISTINCT "member_id" AS "cpn_member_id" 
    FROM  "coupon_collections" 
    WHERE  (("seen" >= '2017-11-02 14:15:43.111597+0000') AND ("seen" <= '2018-11-02 14:15:43.111652+0000')) 
    GROUP BY "member_id"
  ) AS "ex1" ON ("ex1"."cpn_member_id" = "profiles"."member_id") 
  LEFT OUTER JOIN "mobile_data" ON (
    "mobile_data"."member_id" = "profiles"."member_id"
  ) 
  LEFT OUTER JOIN "consents" AS "consents_sms_marketing" 
    ON (("consents_sms_marketing"."member_id" = "profiles"."member_id") 
      AND ("consents_sms_marketing"."name" = 'sms_marketing')) 
WHERE 
  (("community_id" = 123) AND (("push_enabled" IS FALSE) OR ("push_token" IS NULL)) 
    AND ("profiles"."optin_date" :: date >= '2012-01-01T00:00:00+01:00' :: date) 
    AND ("profiles"."optin_date" :: date <= '2018-09-30T00:00:00+02:00' :: date) 
    AND ("ex1"."cpn_member_id" IS NULL)
    AND ("profiles"."msisdn" IS NOT NULL) 
    AND ("data" ->> 'user_status') = 'verified') 
    AND ("consents_sms_marketing"."value" IS TRUE) 
  )

Explain for SELECT:

QUERY PLAN                                                                                                                                                                                              |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Nested Loop  (cost=295510.43..418688.80 rows=1 width=574)                                                                                                                                               |
  ->  Nested Loop Left Join  (cost=295510.00..418680.35 rows=1 width=574)                                                                                                                               |
        Filter: ((mobile_data.push_enabled IS FALSE) OR (mobile_data.push_token IS NULL))                                                                                                               |
        ->  Merge Anti Join  (cost=295509.57..418671.90 rows=1 width=390)                                                                                                                               |
              Merge Cond: (profiles.member_id = coupon_collections.member_id)                                                                                                                           |
              ->  Sort  (cost=108286.51..108286.51 rows=1 width=390)                                                                                                                                    |
                    Sort Key: profiles.member_id                                                                                                                                                        |
                    ->  Bitmap Heap Scan on profiles  (cost=2119.99..108286.50 rows=1 width=390)                                                                                                        |
                          Recheck Cond: ((community_id = 123) AND (msisdn IS NOT NULL))                                                                                                                |
                          Filter: (((optin_date)::date >= '2012-01-01'::date) AND ((optin_date)::date <= '2018-09-30'::date) AND ((data ->> 'user_status'::text) = 'verified'::text))         |
                          ->  Bitmap Index Scan on profiles_community_id_msisdn_index  (cost=0.00..2119.99 rows=52756 width=0)                                                                          |
                                Index Cond: ((community_id = 123) AND (msisdn IS NOT NULL))                                                                                                            |
              ->  Unique  (cost=187223.06..304500.59 rows=470783 width=4)                                                                                                                               |
                    ->  Group  (cost=187223.06..303323.63 rows=470783 width=4)                                                                                                                          |
                          Group Key: coupon_collections.member_id                                                                                                                                       |
                          ->  Gather Merge  (cost=187223.06..300969.72 rows=941566 width=4)                                                                                                             |
                                Workers Planned: 2                                                                                                                                                      |
                                ->  Group  (cost=186223.04..191289.61 rows=470783 width=4)                                                                                                              |
                                      Group Key: coupon_collections.member_id                                                                                                                           |
                                      ->  Sort  (cost=186223.04..188756.33 rows=1013315 width=4)                                                                                                        |
                                            Sort Key: coupon_collections.member_id                                                                                                                      |
                                            ->  Parallel Seq Scan on coupon_collections  (cost=0.00..71285.07 rows=1013315 width=4)                                                                     |
                                                  Filter: ((seen >= '2017-11-02 14:15:43.111597'::timestamp without time zone) AND (seen <= '2018-11-02 14:15:43.111652'::timestamp without time zone)) |
        ->  Index Scan using mobile_data_member_id_index on mobile_data  (cost=0.42..8.44 rows=1 width=184)                                                                                             |
              Index Cond: (member_id = profiles.member_id)                                                                                                                                              |
  ->  Index Scan using consents_member_id_name_index on consents consents_sms_marketing  (cost=0.43..8.45 rows=1 width=4)                                                                               |
        Index Cond: ((member_id = profiles.member_id) AND (name = 'sms_marketing'::text))                                                                                                               |
        Filter: (value IS TRUE)                                                                                                                                                                         |

Explain for create materialized view:

QUERY PLAN                                                                                                                                                                                  |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Nested Loop  (cost=440022.05..570133.95 rows=1 width=574)                                                                                                                                   |
  ->  Nested Loop Left Join  (cost=440021.62..570125.50 rows=1 width=574)                                                                                                                   |
        Filter: ((mobile_data.push_enabled IS FALSE) OR (mobile_data.push_token IS NULL))                                                                                                   |
        ->  Nested Loop Anti Join  (cost=440021.19..570117.05 rows=1 width=390)                                                                                                             |
              Join Filter: (coupon_collections.member_id = profiles.member_id)                                                                                                              |
              ->  Bitmap Heap Scan on profiles  (cost=2119.99..108286.50 rows=1 width=390)                                                                                                  |
                    Recheck Cond: ((community_id = 123) AND (msisdn IS NOT NULL))                                                                                                          |
                    Filter: (((optin_date)::date >= '2012-01-01'::date) AND ((optin_date)::date <= '2018-09-30'::date) AND ((data ->> 'user_status'::text) = 'verified'::text))   |
                    ->  Bitmap Index Scan on profiles_community_id_msisdn_index  (cost=0.00..2119.99 rows=52756 width=0)                                                                    |
                          Index Cond: ((community_id = 123) AND (msisdn IS NOT NULL))                                                                                                      |
              ->  Unique  (cost=437901.20..451237.94 rows=470783 width=4)                                                                                                                   |
                    ->  Group  (cost=437901.20..450060.98 rows=470783 width=4)                                                                                                              |
                          Group Key: coupon_collections.member_id                                                                                                                           |
                          ->  Sort  (cost=437901.20..443981.09 rows=2431955 width=4)                                                                                                        |
                                Sort Key: coupon_collections.member_id                                                                                                                      |
                                ->  Seq Scan on coupon_collections  (cost=0.00..113447.57 rows=2431955 width=4)                                                                             |
                                      Filter: ((seen >= '2017-11-02 14:15:43.111597'::timestamp without time zone) AND (seen <= '2018-11-02 14:15:43.111652'::timestamp without time zone)) |
        ->  Index Scan using mobile_data_member_id_index on mobile_data  (cost=0.42..8.44 rows=1 width=184)                                                                                 |
              Index Cond: (member_id = profiles.member_id)                                                                                                                                  |
  ->  Index Scan using consents_member_id_name_index on consents consents_sms_marketing  (cost=0.43..8.45 rows=1 width=4)                                                                   |
        Index Cond: ((member_id = profiles.member_id) AND (name = 'sms_marketing'::text))                                                                                                   |
        Filter: (value IS TRUE)                                                                                                                                                             |

EDIT: Rubber duck for the win. After Writing this question I finally notices that with creating a view planner decides to use nested loop instead of hash/merge join. As both sides are relatively big, this is horribly wrong. I forced the planner to change its mind by issuing set enable_nestloop = false and now it runs in 10 seconds, which is totally acceptable (plan below).

The question remains - how to optimize the structure to let planner choose efficient join method on it's own, without resorting to such dirty tricks?

Promised explain analyze:

QUERY PLAN                                                                                                                                                                                                  |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Hash Join  (cost=603547.70..699558.89 rows=1 width=573) (actual time=7922.084..9402.481 rows=17036 loops=1)                                                                                                 |
  Hash Cond: (consents_sms_marketing.member_id = profiles.member_id)                                                                                                                                        |
  ->  Seq Scan on consents consents_sms_marketing  (cost=0.00..90423.46 rows=1490059 width=4) (actual time=0.010..857.382 rows=1658797 loops=1)                                                             |
        Filter: ((value IS TRUE) AND (name = 'sms_marketing'::text))                                                                                                                                        |
        Rows Removed by Filter: 2420637                                                                                                                                                                     |
  ->  Hash  (cost=603547.68..603547.68 rows=1 width=573) (actual time=7921.077..7921.077 rows=18170 loops=1)                                                                                                |
        Buckets: 16384 (originally 1024)  Batches: 2 (originally 1)  Memory Usage: 3969kB                                                                                                                   |
        ->  Hash Right Join  (cost=565515.53..603547.68 rows=1 width=573) (actual time=7239.595..7904.829 rows=18170 loops=1)                                                                               |
              Hash Cond: (mobile_data.member_id = profiles.member_id)                                                                                                                                       |
              Filter: ((mobile_data.push_enabled IS FALSE) OR (mobile_data.push_token IS NULL))                                                                                                             |
              Rows Removed by Filter: 2750                                                                                                                                                                  |
              ->  Seq Scan on mobile_data  (cost=0.00..35173.38 rows=762338 width=183) (actual time=0.009..211.746 rows=762358 loops=1)                                                                     |
              ->  Hash  (cost=565515.51..565515.51 rows=1 width=390) (actual time=7235.692..7235.692 rows=20920 loops=1)                                                                                    |
                    Buckets: 16384 (originally 1024)  Batches: 4 (originally 1)  Memory Usage: 3969kB                                                                                                       |
                    ->  Merge Anti Join  (cost=546291.23..565515.51 rows=1 width=390) (actual time=4172.760..7203.434 rows=20920 loops=1)                                                                   |
                          Merge Cond: (profiles.member_id = coupon_collections.member_id)                                                                                                                   |
                          ->  Sort  (cost=108286.51..108286.51 rows=1 width=390) (actual time=226.020..270.103 rows=59822 loops=1)                                                                          |
                                Sort Key: profiles.member_id                                                                                                                                                |
                                Sort Method: external merge  Disk: 23384kB                                                                                                                                  |
                                ->  Bitmap Heap Scan on profiles  (cost=2119.99..108286.50 rows=1 width=390) (actual time=26.365..135.034 rows=59822 loops=1)                                               |
                                      Recheck Cond: ((community_id = 123) AND (msisdn IS NOT NULL))                                                                                                        |
                                      Filter: (((optin_date)::date >= '2012-01-01'::date) AND ((optin_date)::date <= '2018-09-30'::date) AND ((data ->> 'user_status'::text) = 'verified'::text)) |
                                      Rows Removed by Filter: 2375                                                                                                                                          |
                                      Heap Blocks: exact=23559                                                                                                                                              |
                                      ->  Bitmap Index Scan on profiles_community_id_msisdn_index  (cost=0.00..2119.99 rows=52756 width=0) (actual time=17.690..17.690 rows=62248 loops=1)                  |
                                            Index Cond: ((community_id = 123) AND (msisdn IS NOT NULL))                                                                                                    |
                          ->  Unique  (cost=438004.72..451344.19 rows=470784 width=4) (actual time=3946.732..6637.208 rows=743564 loops=1)                                                                  |
                                ->  Group  (cost=438004.72..450167.23 rows=470784 width=4) (actual time=3946.729..6134.888 rows=743564 loops=1)                                                             |
                                      Group Key: coupon_collections.member_id                                                                                                                               |
                                      ->  Sort  (cost=438004.72..444085.97 rows=2432502 width=4) (actual time=3946.725..5048.156 rows=2367134 loops=1)                                                      |
                                            Sort Key: coupon_collections.member_id                                                                                                                          |
                                            Sort Method: external merge  Disk: 32760kB                                                                                                                      |
                                            ->  Seq Scan on coupon_collections  (cost=0.00..113475.11 rows=2432502 width=4) (actual time=0.014..1660.434 rows=2384812 loops=1)                              |
                                                  Filter: ((seen >= '2017-11-02 14:15:43.111597'::timestamp without time zone) AND (seen <= '2018-11-02 14:15:43.111652'::timestamp without time zone))     |
                                                  Rows Removed by Filter: 2456783                                                                                                                           |
Planning time: 0.745 ms                                                                                                                                                                                     |
Execution time: 9455.154 ms                                                                                                                                                                                 |
0

The solution was to take a look at pg_stats and notice that there is very low correlation for some columns. The adjusting the stats for those relevant columns using:

ALTER TABLE coupon_collections ALTER COLUMN member_id SET STATISTICS 1000

Now creating view is slower by a very reasonable factor.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.