title: How to improve performance of a nested loop operation which queries a postgres database and compares RegEx
body: <br/>
  I am facing some time performance issue. My scenario is the following:
  <p>I have a database table <code>Product</code> which stores products of different vendors in a table.</p>
  <pre><code>Product
  +------------------+-------------------+
  | Name             | Vendor            |
  +==================+===================+
  | iPhone_12        | apple             |
  +------------------+-------------------+
  | iPhone_11        | apple             |
  +------------------+-------------------+
  | Samsung Galaxy   | samsung           |
  +------------------+-------------------+
  </code></pre>
  <p>I also have a table <code>Subscription</code> where my customers can &quot;subscribe&quot; to products that they own. I allow my customers to use <strong>RegEx</strong> for subscriptions so the <code>Subscription</code> table might look like this:</p>
  <pre><code>Subscription
  +----+------------------+-------------------+-------------+
  | Id | Name             | Vendor            | CustomerId  |
  +====+==================+===================+=============+
  | 0  | iPhone_*         | apple             | 1           |
  +----+------------------+-------------------+-------------+
  | 1  | iPad_*           | app*              | 2           |
  +----+------------------+-------------------+-------------+
  </code></pre>
  <p>Now I have a website where my customer can view all his subscribed products.<br/></p>
  <ol>
  <li>For instance <code>subscription[Id=0]</code> would match <strong>any</strong> iPhone_* (iPhone_12, iPhone_11 in this case) from the product table.<br/></li>
  <li>For <code>subscription[Id=1]</code> it will match <strong>any</strong> iPad_* from <strong>any</strong> vendor that starts with <code>app</code> from the product table.</li>
  </ol>
  <p><strong>The issue:</strong><br/>
  In my case I have customers which have 500+ suscriptions and my products table contains +500k products. Currently I am querying all subscriptions, then iterating over all of them and for each I will query all products and do a string RegEx comparison. Here a sample example, it's not the actual code as I made this code example up but it represents how I am doing it in fact:</p>
  <pre><code>const subscribedProducts = []
  for (const subscription : db.findSubscriptionsByCustomerId(1)) {
     for (const product : db.findProducts()) {
        RegEx r1 = RegEx.parse(subscription.name)
        RegEx r2 = RegEx.parse(subscription.vendor)
  
        if (r1.match(product.name)) &amp;&amp; r2.match(product.vendor)) {
           subscribedProducts.push(subscription)
        }
     }
  }
  </code></pre>
  <p>This makes the whole system VERY slow. I know there are <code>Patterns</code> for SQL queries but they are not as advanced as RegEx.</p>
  <p>Does someone have an idea how I could improve that? Code-wise or database-wise or in any other way? This is very important for me.</p>
  <p>Thanks in advance!</p>