Re: BUG #1435: Optimizer not using index on large tables when inner joining two views

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Yary Hluchan" <not(dot)com(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1435: Optimizer not using index on large tables when inner joining two views
Date: 2005-01-24 22:21:03
Message-ID: 25577.1106605263@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Yary Hluchan" <not(dot)com(at)gmail(dot)com> writes:
> My reading of it is that the optimizer isn't applying a distributive rule:
> condition(a union b) == condition(a) union condition(b).

No, the correct reading is that a UNION subquery is planned
independently of the surrounding query. This is not likely to change
soon, as it would involve some rather wholesale changes.

My advice is to lose the open/closed distinction and fold those tables
into single tables with an extra boolean flag column. I suppose the
point of this database layout is to provide fast access to the "open"
subsets, but you could probably achieve that by making partial indexes,
eg

create index vote_open_ids on vote (id) where is_open;

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Pavel Stehule 2005-01-24 22:50:34 plperl trigger crash backend 8.0.rc4
Previous Message Martin Pitt 2005-01-24 21:57:49 Insecure temporary file usage in developer/build tools