To: Craig James <craig_james@emolecules.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Query on view radically slower than query on underlying table 
In-reply-to: <4D6BF648.8090105@emolecules.com> 
References: <201102281038.04945.cousinmarc@gmail.com>
	<4D6B9BC9.1000404@enterprisedb.com>
	<4D6BE95C.3090102@emolecules.com> <27415.1298919456@sss.pgh.pa.us>
	<4D6BF648.8090105@emolecules.com>
Comments: In-reply-to Craig James <craig_james@emolecules.com>
	message dated "Mon, 28 Feb 2011 11:23:52 -0800"
Date: Mon, 28 Feb 2011 14:58:28 -0500
Message-ID: <28838.1298923108@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Craig James <craig_james@emolecules.com> writes:
> Then I thought maybe putting a foreign-key constraint on table "my_version" would solve the problem:

>    alter table my_version add constraint fk_my_view foreign key(version_id)
>    references registry.version(version_id) on delete cascade;

> That way, the planner would know that every key in table "my_version" has to also be in table "version", thus avoiding that part about "forcing the other join to be done in toto".  But the foreign-key constraint makes no difference, it still does the full join and takes 65 seconds.

That's just wishful thinking I'm afraid.  The planner doesn't currently
make any deductions whatsoever from the presence of a foreign key
constraint; and even if it did, I'm not sure that this would help it
decide that a join order constraint could safely be dropped.

			regards, tom lane