From: Jocelyn Turcotte <turcotte.j@gmail.com>
To: John A Meinel <john@arbash-meinel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inner join on two OR conditions dont use index
Date: Wed, 25 May 2005 17:22:10 -0400
Message-ID: <688c0b7505052514227ee968ce@mail.gmail.com> (raw)
In-Reply-To: <4294DDAC.1060908@arbash-meinel.com>
References: <688c0b7505052513127df47894@mail.gmail.com>
<4294DDAC.1060908@arbash-meinel.com>
Thanks John
it dont seems to work, but in my context I only needed data from the
rt_node table so I tried this:
select *
from rt_node n
where node_id = 2
and exists (select edge_id from rt_edge where start_node_id =
n.node_id or end_node_id = n.node_id)
and it gave me this plan (even if I remove the stupid node_id = 2 condition):
Index Scan using rt_node_pkey on rt_node n (cost=0.00..6.15 rows=1 width=25)
Index Cond: (node_id = 2)
Filter: (subplan)
SubPlan
-> Index Scan using rt_edge_start_node, rt_edge_end_node on
rt_edge (cost=0.00..12.56 rows=4 width=4)
Index Cond: ((start_node_id = $0) OR (end_node_id = $0))
this time it use my two indexes, maybe because he know that the same
value is compared in the two condition... I should ask my mother if
she got an idea, mothers know a lot of stuff!
On 5/25/05, John A Meinel <john@arbash-meinel.com> wrote:
> Jocelyn Turcotte wrote:
>
> >Hi all
> >i dont know if this is normal, but if yes i would like to know why and
> >how I could do it another way other than using unions.
> >
> >
>
> The only thing that *might* work is if you used an index on both keys.
> So if you did:
>
> CREATE INDEX rt_edge_start_end_node ON rt_edge(start_node_id,end_node_id);
>
> The reason is that in an "OR" construct, you have to check both for being true. So in the general case where you don't know the correlation between the columns, you have to check all of the entries, because even if you know the status of one side of the OR, you don't know the other.
>
> Another possibility would be to try this index:
>
> CREATE INDEX rt_edge_stare_or_end ON rt_edge(start_node_id OR end_node_id);
>
> I'm not sure how smart the planner can be, though.
>
> John
> =:->
>
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: turcotte.j@gmail.com, john@arbash-meinel.com
Subject: Re: Inner join on two OR conditions dont use index
In-Reply-To: <688c0b7505052514227ee968ce@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox