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;


Nested Loop  (cost=1.01..135956337.96 rows=1 width=8) (actual time=393.040..50792.689 rows=20 loops=1)
  ->  Nested Loop  (cost=0.58..135956333.67 rows=1 width=16) (actual time=393.018..50790.822 rows=547 loops=1)
        ->  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)
        ->  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)
  ->  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)
  • Please consider reading about Asking query performance questions – mustaccio Apr 27 at 20:39
  • How many rows in "product" satisfy classtype = 'loan' ? Based on your shown execution plan, we don't know either how many actually do, nor how many PostgreSQL thinks do. Please show us EXPLAIN ANALYZE select id from product where classtype = 'loan' – jjanes Apr 28 at 20:14
  • A nested loop from product to transaction0 to journ_notes might be good (depending on your answer to my prior comment), but there is apparently no index on journ_notes (jdoid) ? – jjanes Apr 28 at 20:24

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.

| improve this answer | |
  • Sorry, the PG version is 9.6. Going to do the tests. I will come back with the results soon. @Laurenz Albe – Felipe Apr 28 at 13:00
  • In 9.6, parallel query cannot do much yet. – Laurenz Albe Apr 28 at 13:41

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.