Return to Question

Edit - 3

Added two new indexes on lower(brand) and created_at in products

CREATE INDEX products_lower_brand_created_at_idx ON products (created_at, lower(brand));
CREATE INDEX brands_lower_brand_idx ON products (lower(name));

Here is the EXPLAIN plan for the same query

Update on products  (cost=7.97..435.50 rows=1 width=3187)
  ->  Nested Loop Semi Join  (cost=7.97..435.50 rows=1 width=3187)
        Join Filter: (products.id = "ANY_subquery".id)
        ->  Hash Join  (cost=7.54..426.80 rows=77 width=3159)
              Hash Cond: (lower((brands.name)::text) = lower((products.brand)::text))
              ->  Seq Scan on brands  (cost=0.00..321.76 rows=15476 width=24)
              ->  Hash  (cost=7.53..7.53 rows=1 width=3149)
                    ->  Index Scan using products_lower_brand_created_at_idx on products  (cost=0.43..7.53 rows=1 width=3149)
                          Index Cond: (created_at >= ('now'::cstring)::date)
                          Filter: (clean_brand_id IS NULL)
        ->  Materialize  (cost=0.43..7.54 rows=1 width=32)
              ->  Subquery Scan on "ANY_subquery"  (cost=0.43..7.54 rows=1 width=32)
                    ->  Limit  (cost=0.43..7.53 rows=1 width=4)
                          ->  Index Scan using products_lower_brand_created_at_idx on products products_1  (cost=0.43..7.53 rows=1 width=4)
                                Index Cond: (created_at >= ('now'::cstring)::date)
                                Filter: (clean_brand_id IS NULL)

These changes are implemented in local database and number of records are around .4 million. Can anyone help to findout if the new index can help in improving the query execution time

Edit - 2

After removing the duplicate brand names from brand table and its reference from products table, here is the EXPLAIN plan for the SQL query

Update on products  (cost=1442396.71..1443511.50 rows=1 width=3232)
   ->  Hash Join  (cost=1442396.71..1443511.50 rows=1 width=3232)
         Hash Cond: (lower((brands.name)::text) = lower((products.brand)::text))
         ->  Seq Scan on brands  (cost=0.00..848.57 rows=42257 width=24)
         ->  Hash  (cost=1442396.70..1442396.70 rows=1 width=3222)
               ->  Nested Loop  (cost=1441549.13..1442396.70 rows=1 width=3222)
                     ->  HashAggregate  (cost=1441548.70..1441549.70 rows=100 wi
dth=32)
                           Group Key: "ANY_subquery".id
                           ->  Subquery Scan on "ANY_subquery"  (cost=66088.59..
1441548.45 rows=100 width=32)
                                 ->  Limit  (cost=66088.59..1441547.45 rows=100
width=4)
                                       ->  Bitmap Heap Scan on products products
_1  (cost=66088.59..1936712.63 rows=136 width=4)
                                             Recheck Cond: (clean_brand_id IS NU
LL)
                                             Filter: (created_at >= ('now'::cstr
ing)::date)
                                             ->  Bitmap Index Scan on index_prod
ucts_on_clean_brand_id  (cost=0.00..66088.56 rows=1370683 width=0)
                                                   Index Cond: (clean_brand_id I
S NULL)
                     ->  Index Scan using products_pkey on products  (cost=0.43.
.8.46 rows=1 width=3194)
                           Index Cond: (id = "ANY_subquery".id)
                           Filter: ((clean_brand_id IS NULL) AND (created_at >=
('now'::cstring)::date))
(18 rows)

another factor for less time taken by the second EXPLAIN Plan is in the earlier execution number of new records are around .8 million, and in the second execution number of new records are much less(exact number not known)

Can anyone help me to find out if any of the existing indexes are utilized in the SQL query execution or not. If I can improve the execution of this query by creating any joint index on multiple columns.