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 > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
