Skip site navigation (1) Skip section navigation (2)

Re: Query on view radically slower than query on underlying table

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Craig James <craig_james(at)emolecules(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query on view radically slower than query on underlying table
Date: 2011-02-28 19:58:28
Message-ID: 28838.1298923108@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Craig James <craig_james(at)emolecules(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Selva manickarajaDate: 2011-03-01 01:48:14
Subject: Re: Load and Stress on PostgreSQL 9.0
Previous:From: Craig JamesDate: 2011-02-28 19:23:52
Subject: Re: Query on view radically slower than query on underlying table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group