Re: Idea - optimising (left) joins?

On Wed, 27 Jul 2005, Dawid Kuroczko wrote:

> Hello. I was just wondering, assume we have such tables:
>
> CREATE TABLE data (
> foo text,
> somename_id integer not null references (somenames)
> );
>
> CREATE TABLE somenames (
> somename_id serial PRIMARY KEY
> somename text NOT NULL
> );
>
> And a view:
>
> CREATE someview AS SELECT foo,somename FROM data NATURAL JOIN somenames;
>
> ...and a user does:
> SELECT foo FROM data order by foo LIMIT 1 OFFSET 1000;
>
> ...we could assume some of the things:
> 1. as somename_id references somenames, and it is joined with somenames,
> there will always be at least one row in somenames for each row of data.
> 2. as the somename_id is primary key, there will be exactly one row.
> 3. 1 and 2 together -- no matter if we join somenames or not, we'll get
> same number of rows
> 4. So if columns from somenames are not used for anything, we can skip them.
> No need to join at all.

Note that I believe the above only holds if also the reference is
immediate (technically, also, the key must be immediate but we don't
support non-immediate primary keys currently). I'm uncertain whether the
condition holds inside triggers eventually coming from updates to data or
somenames as well as from my last reading of SQL2003 I believe it's
possible to get into places where you're running before the constraint
check occurs.

> Why bother? There are cases where data is normalised and there is
> a view which joins main table with few tables similar to somenames
> mentioned here. If PostgreSQL could skip even looking at the tables
> that will not be used, it could give advantage; the bigger the data,
> the more advantage.
>
> What do you think about it? Is it worth the effort?

Possibly, one big question is how much cost does it add to queries that
the optimization doesn't apply in order to try to see if it does.