From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig James <craig_james@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query on view radically slower than query on underlying table
Date: Mon, 28 Feb 2011 14:58:28 -0500
Message-ID: <28838.1298923108@sss.pgh.pa.us> (raw)
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>
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
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: tgl@sss.pgh.pa.us, craig_james@emolecules.com
Subject: Re: Query on view radically slower than query on underlying table
In-Reply-To: <28838.1298923108@sss.pgh.pa.us>
* 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