From: | Tsirkin Evgeny <tsurkin(at)mail(dot)jct(dot)ac(dot)il> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | index for or relation |
Date: | 2004-09-28 10:25:12 |
Message-ID: | Pine.LNX.4.58.0409281217090.3327@p-roman.jct.ac.il |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Madsen | 2004-09-28 13:12:25 | Vacuum full - disk space eaten by WAL logfiles |
Previous Message | Tom Lane | 2004-09-27 23:40:18 | Re: killing a hung postgres process brings down the Postgres database server on MAC OS X! |