Edit: Proposed this question as a LEFT JOIN problem, but as @wildplasser pointed out, due to my WHERE clause I was actually doing a JOIN all along. Adjusting the question from LEFT JOIN to JOIN.

The problem:

I'm working with a query which JOINs three large tables (millions of rows) together, the problem is: it's terribly slow.

The summarized question:

I think the hardware is more than up to spec, the drive is fast enough and querying the tables directly is also very fast. Yet when I JOIN them together, it becomes very slow (gut feeling says incorrect ordering in the query - but my rearrange attempts have not led to a significant speedup).

Is there a solution to speed up this JOIN query, or am I out of luck? What am I missing here?

The tables:

First some information on the three tables with respect to the query:

"reports": has 6 million rows. 9 columns.

"report_drugs": has 20 million rows, 7 columns. The column "drug" (int) has a B-Tree index and a STATISTICS target of 10.000

"report_adverses": has 20 million rows, 5 columns. The column "adverse" (text) has a B-Tree index and a STATISTICS target of 10.000

The "reports" table holds main report information, the other two hold drug and adverse information related to the "reports" table. They all share a report ID ("id" column in "reports", "rid" column in the other two).

All three tables have been VACUUM ANALYZEd recently.

The hardware:

The hardware: I'm running the cluster from an SSD drive, as a traditional HDD could not even manage the query in under 5 minutes. The system has a total memory of 24 GB, runs on Debian and uses an 4Ghz 8 core i7-4790 processor. Some important postgresql.conf readouts:

  • shared_buffers = 4GB
  • work_mem = 10MB
  • checkpoint_segments = 50
  • checkpoint_completion_target = 0.9
  • autovacuum = on

The database action:

SELECT r.id, r.age, r.gender, r.created
FROM reports r
JOIN report_drugs d ON d.rid = r.id
JOIN report_adverses a ON a.rid = r.id 
WHERE a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific back pain', 'back pain']) 
AND d.drug = ANY (ARRAY[359, 360, 361, 362, 363]) ORDER BY r.created;

Executing this query will deliver the following plan (EXPLAIN ANALYZE):

Sort  (cost=105773.63..105774.46 rows=333 width=76) (actual time=5143.162..5143.185 rows=448 loops=1)
   Sort Key: r.created
   Sort Method: quicksort  Memory: 60kB
   ->  Nested Loop  (cost=1.31..105759.68 rows=333 width=76) (actual time=54.784..5142.872 rows=448 loops=1)
     Join Filter: (d.rid = a.rid)
     ->  Nested Loop  (cost=0.87..94657.59 rows=14005 width=72) (actual time=0.822..2038.952 rows=14199 loops=1)
         ->  Index Scan using report_drugs_drug_idx on report_drugs d  (cost=0.44..500.28 rows=14005 width=31) (actual time=0.669..3.900 rows=14199 loops=1)
               Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[]))
         ->  Index Scan using reports_id_key on reports r  (cost=0.43..6.71 rows=1 width=41) (actual time=0.143..0.143 rows=1 loops=14199)
               Index Cond: (id = d.rid)
     ->  Index Scan using report_adverses_rid_idx on report_adverses a  (cost=0.44..0.78 rows=1 width=12) (actual time=0.218..0.218 rows=0 loops=14199)
           Index Cond: (rid = r.id)
           Filter: (adverse = ANY ('{"back pain - nonspecific","nonspecific back pain","back pain"}'::text[]))
           Rows Removed by Filter: 5
Planning time: 13.994 ms
Execution time: 5143.235 ms

This runs well over 5 seconds. Once ran, it can pull off the data from the OS cache and it runs (with the exact same query plan) in under 100 ms.

Because of this fact (using OS cache = less than 100ms) I'm a bit worried that it might not be the database, but the actual storage that is slow. Yet, again, it is running on a brand new SSD. Benchmarking this drive via hdparm:

Timing cached reads:   33946 MB in  2.00 seconds = 16990.37 MB/sec
Timing buffered disk reads: 968 MB in  3.01 seconds = 322.10 MB/sec

This would indicate adequate speed.

Another observation: If I query only one of the tables directly using the same WHERE clause, it is fast. For example, querying the "report_drugs" table with the "drug" column in the WHERE clause:

SELECT reason
FROM report_drugs 
WHERE drug = ANY (ARRAY[359, 360, 361, 362, 363]);

Results in the following plan and executes in under 5 milliseconds (no OS cache used):

Index Scan using report_drugs_drug_idx on report_drugs  (cost=0.44..500.28 rows=14005 width=27) (actual time=0.621..4.510 rows=14199 loops=1)
  Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[]))
Planning time: 6.939 ms
Execution time: 4.759 ms

migrated from stackoverflow.com Apr 25 '16 at 8:22

This question came from our site for professional and enthusiast programmers.

  • 1
    NOTE: WHERE a.adverse = ANY(...) referring to a.XXX transforms your left join into a plain join. (similar for d.yyy) – wildplasser Apr 24 '16 at 23:28
  • @wildplasser Ah, that is an important gotcha ... thanks for pointing that out! These conditions should be moved to the ON clause to retain a LEFT JOIN? – Timusan Apr 24 '16 at 23:30
  • trivial, my dear Watson – wildplasser Apr 24 '16 at 23:33
  • "has a B-Tree index" Just a suggestion: try hash indexes instead. – Abelisto Apr 25 '16 at 9:09
  • 1
    Could you run your query with adding: WHERE a.rid = d.rid and tell us if the times and/or execution plan is different? – ypercubeᵀᴹ Apr 25 '16 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.