title: Optimizing very slow PostgreSQL LEFT JOIN between two large tables
body: <p>For this case I have two tables in my database.  One storing transactions entered by a user and another storing accounts that each transaction can be assigned to.  Accounts have the ability to be marked as inactive and users also have the ability to enter transactions that aren't assigned to an account.</p>
  <p>I have a query to grab the first 400 transactions a user has entered where the account is active or the user didn't assign the transaction to an account.</p>
  <p>The query looks like this:</p>
  <pre class="lang-sql prettyprint-override"><code>select t.*
  from transactions_20000 as t 
  left join accounts as a 
      on t.account_id = a.id 
  where t.user_id = 1 
      and (a.status=1 or t.account_id=0) 
  order by t.date desc, t.transaction_type asc, t.id desc 
  limit 400 offset 0;
  </code></pre>
  <p>When I run <code>EXPLAIN ANALYZE</code> on the query, I get the following result: <a href="https://explain.depesz.com/s/YM2v" rel="nofollow noreferrer">https://explain.depesz.com/s/YM2v</a> The problem seems to be in step #5 where it takes over a second and a half to process that part of the query.  While a total run time of 1.6 seconds doesn't seem like a lot, it really starts to add up when there are thousands of users hitting that query hourly when they load the app.  Obviously changing the limit won't really affect anything since that's not where the slow part is.</p>
  <p>I'm at a bit of a loss in trying to figure out how to speed this query up.  I know it shouldn't take this long and it's bugging me that I'm probably missing something easy.</p>
  <p>The <code>transactions</code> table has a 4 million rows in it, only about 10k of which are assigned to the user.  The <code>accounts</code> table has about 2 million rows in it.</p>
  <p>For reference, here are the two tables:</p>
  <pre><code>Table &quot;public.accounts&quot;
      Column    |              Type              | Collation | Nullable |               Default                
  --------------+--------------------------------+-----------+----------+--------------------------------------
   id           | bigint                         |           | not null | nextval('accounts_id_seq'::regclass)
   user_id      | integer                        |           | not null | 
   name         | character varying(250)         |           |          | 
   type_id      | integer                        |           | not null | 
   status       | integer                        |           | not null | 
   currency_id  | integer                        |           |          | 1
   credit_limit | numeric(15,2)                  |           |          | 0
   group_id     | integer                        |           |          | 0
   created_at   | timestamp(0) without time zone |           |          | 
   updated_at   | timestamp(0) without time zone |           |          | 
  Indexes:
      &quot;accounts_pkey&quot; PRIMARY KEY, btree (id)
      &quot;a_user_id&quot; btree (user_id)
  </code></pre>
  <pre><code>Table &quot;public.transactions_20000&quot;
         Column       |            Type             | Collation | Nullable |                    Default                     
  --------------------+-----------------------------+-----------+----------+------------------------------------------------
   id                 | bigint                      |           | not null | nextval('transactions_20000_id_seq'::regclass)
   user_id            | integer                     |           | not null | 
   date               | timestamp without time zone |           | not null | 
   amount             | numeric(15,2)               |           | not null | 
   transaction_type   | integer                     |           | not null | 0
   description        | character varying(255)      |           |          | 
   account_id         | integer                     |           |          | 0
   category_id        | integer                     |           |          | 0
   jive               | integer                     |           |          | 0
   specialstatus      | character varying(50)       |           |          | 
   parent             | integer                     |           |          | 0
   ccparent           | integer                     |           |          | 0
   related_transfer   | character varying(50)       |           | not null | 0
   created_at         | timestamp without time zone |           | not null | now()
   check_num          | character varying(32)       |           |          | 
   payee              | character varying(255)      |           |          | 
   memo               | character varying(255)      |           |          | 
   additional_user_id | integer                     |           |          | 0
   initial_balance    | boolean                     |           |          | 
   jived_date         | timestamp without time zone |           |          | 
   attachment         | character varying(100)      |           |          | 
   add_source         | character(25)               |           |          | 
  Indexes:
      &quot;transactions_20000_unique_pkey&quot; PRIMARY KEY, btree (id)
      &quot;transactions_20000_user_ccparent_idx&quot; btree (user_id, ccparent) WHERE ccparent &gt; 0
      &quot;transactions_20000_user_date_idx&quot; btree (user_id, date)
      &quot;transactions_20000_user_id_key&quot; btree (user_id)
  </code></pre>
  <p>Does anyone see anything obvious that's causing this query to take so long?</p>