Message-ID: <4294DDAC.1060908@arbash-meinel.com>
Date: Wed, 25 May 2005 15:18:52 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Jocelyn Turcotte <turcotte.j@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inner join on two OR conditions dont use index
References: <688c0b7505052513127df47894@mail.gmail.com>
In-Reply-To: <688c0b7505052513127df47894@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigBA2C45A3BD41EF23FF6EC9B5"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigBA2C45A3BD41EF23FF6EC9B5
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------enigBA2C45A3BD41EF23FF6EC9B5
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFClN2sJdeBCYSNAAMRAkE2AJ98+ARuCFZwm8ndvmzXAgJcVi8SOgCfUTN+
p+XUzT34SFKm2f3JjUiyCnU=
=imqt
-----END PGP SIGNATURE-----

--------------enigBA2C45A3BD41EF23FF6EC9B5--