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
>
>
>
