Thread: index for or relation

Hi All !
Maybe that a basic question ,sorry.
I have to 2 tables :
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 yaadid              | numeric(19,0)               |
 populationtype      | character varying(50)       |
 population          | numeric(20,0)               |
 insert_trans_id     | numeric(16,0)               |
 invalidate_trans_id | numeric(16,0)               |
nextval('public.population2yaad_journal_journalid_seq'::text)
Indexes: population2yaad_insert_trans_id_idx btree (insert_trans_id),
         population2yaad_invalidate_trans_id_idx btree (invalidate_trans_id)

and
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 action         | character varying(50)       |
 transaction_id | numeric(16,0)               |
 worker_login   | character varying(10)       |
 time           | timestamp without time zone |
 param          | character varying(100)      |
 studentid      | numeric(9,0)                |
Indexes: journal_action_idx btree ("action"),
         journal_trans_id_idx btree (transaction_id)


I am trying to join it like this:

select pj.populationtype,getPopulationName(pj.populationtype,pj.population) as populationname,pj.population,pj.yaadid,
                pj.insert_trans_id,pj.invalidate_trans_id,
                j.time,w.login as worker_login,j.action,j.transaction_id as j_transaction_id
             from journal j
             INNER JOIN population2yaad_journal pj on (j.transaction_id=pj.insert_trans_id
                    OR j.transaction_id=pj.invalidate_trans_id)
             where j.action = 'add_yaad' or j.action = 'delete_yaad' or  j.action = 'change_yaad' order by j.time

However this does not use the indexes because of the OR in the INNER JOIN.
How should i create indexes to make this work right?

Thanks
--
Evgeny.

> select pj.populationtype,getPopulationName(pj.populationtype,pj.population) as
populationname,pj.population,pj.yaadid,
>                 pj.insert_trans_id,pj.invalidate_trans_id,
>                 j.time,w.login as worker_login,j.action,j.transaction_id as j_transaction_id
>              from journal j
>              INNER JOIN population2yaad_journal pj on (j.transaction_id=pj.insert_trans_id
>                     OR j.transaction_id=pj.invalidate_trans_id)
>              where j.action = 'add_yaad' or j.action = 'delete_yaad' or  j.action = 'change_yaad' order by j.time

Perhaps you can break apart the query by using UNION (ALL). Something
like:

SELECT ...
FROM journal j
JOIN population2yaad_journal pg
ON (j.transaction_id=pj.insert_trans_id)
WHERE j.action = 'add_yaad'
OR j.action = 'delete_yaad'
OR  j.action = 'change_yaad'
UNION ALL
SELECT ...
FROM journal j
JOIN population2yaad_journal pg
ON (j.transaction_id=pj.invalidate_trans_id)
WHERE (j.action = 'add_yaad'
OR j.action = 'delete_yaad'
OR  j.action = 'change_yaad')
/* Prevent duplicates from UNION *ALL*: */
AND j.transaction_id <> pj.insert_trans_id

> However this does not use the indexes because of the OR in the INNER JOIN.
> How should i create indexes to make this work right?

If this still doesn't work, then perhaps your indexes are not selective
enough or perhaps you need to ANALYZE your table.