Query performance improvement Can I improve performance of this query?

SELECT transact.id
           FROM transaction0 transact
           JOIN journ_notes journ ON transact.journal_jdoid = journ.jdoid
           JOIN product account ON transact.product_id = account.id
          WHERE account.classtype::text = 'loan'::text
          AND journ.notes_jdoid IS NOT null;


explain:

Nested Loop  (cost=1.01..135956337.96 rows=1 width=8) (actual time=393.040..50792.689 rows=20 loops=1)
  -&gt;  Nested Loop  (cost=0.58..135956333.67 rows=1 width=16) (actual time=393.018..50790.822 rows=547 loops=1)
        -&gt;  Seq Scan on journ_notes journ  (cost=0.00..469345.58 rows=30466258 width=8) (actual time=0.007..7229.810 rows=30466263 loops=1)
              Filter: (notes_jdoid IS NOT NULL)
        -&gt;  Index Scan using i_transaction0_journal_jdoid on transaction0 transact  (cost=0.58..4.44 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=30466263)
              Index Cond: (journal_jdoid = journ.jdoid)
  -&gt;  Index Scan using i_product_id on product account  (cost=0.43..4.28 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=547)
        Index Cond: (id = transact.product_id)
        Filter: ((classtype)::text = 'loan'::text)
        Rows Removed by Filter: 1
Planning time: 0.464 ms
Execution time: 50792.743 ms


I already have the index listed on the explain log, but also the unused ones below:

create INDEX  _i_transaction0_product_id_id on transaction0(product_id, id)

create INDEX  _i_product_classtype_id on product(classtype, id)
You are effectively asking PostgreSQL to scan the complete tables, since both of your WHERE conditions are not selective at all.

Since journ_notes contains some 30 million rows, that will always take some time.

PostgreSQL's estimates seem to be good, and the nested loop join is probably chosen because the other tables are either big enough to make a hash join unattractive, or work_mem is set too small to contain the resulting hash.

Try again with a large setting for work_mem and see if you get better results. Verify that PostgreSQL is doing the right thing by temporarily disabling nested loop joins with

SET enable_nestloop = off;


and checking if the execution becomes slower or not.

Other than that, perhaps parallel query can make it faster (though not cheaper), but you don't tell us your version.