Re: Slow join over three tables

Hi!!

what happens if you change your query to:

SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug
  FROM reports r
  INNER JOIN report_drugs d USING (rid) 
  INNER JOIN report_adverses a USING (rid) 
 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;

I have seen differences in time between "ON vs USING"....i prefer "USING" when is possible

> >
> > All tables have indexes on the "id"/"rid" columns and on the "drug"/"adverse" columns.
> >
> > The query:
> >
> > SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug
> > 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;
> >
> I would suggest a few experiments to see how you can modify the plans
> available to the optimizer:
>
> 1. CREATE INDEX ON report_drugs (drug, rid)
> 2. CREATE INDEX ON report_adverses (adverse, rid)
> 3. CREATE INDEX ON report (id, created)
>
> Re-run EXPLAIN ANALYZE of your query after each of these steps to see
> how each one affects planning.  You might also try two variants of the
> query at each step, with and without the ORDER BY.
>
> Note, the index column order in (1) and (2) above is very important.
>
>
> Karl
>
>
>