Re: Idea - optimising (left) joins?

From: Tino Wildenhain <tino(at)wildenhain(dot)de>
To: Dawid Kuroczko <qnex42(at)gmail(dot)com>
Cc: Postgres Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Idea - optimising (left) joins?
Date: 2005-07-27 09:28:32
Message-ID: 1122456512.30587.135.camel@Andrea.peacock.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Am Mittwoch, den 27.07.2005, 10:40 +0200 schrieb Dawid Kuroczko:
> 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.
>
> Other scenario:
> 1. someone_id is a simple integer, but the join is left join. The join is
> performed with somename_id in somenames (primary key), so the
> standard join would return 0 or 1 rows. left join returns 1 row for
> each row in data table.
> 2. If somenames columns are not used -- we can skip them.
>
> 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?

Afaic its already done ;) In fact, views are implemented as
rules - so they are kind of "include" in your query and
are optimized just the same way as if you type the full query
there.

See explain / explain analyze.

Regards
Tino

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Larry Rosenman 2005-07-27 11:03:45 Re: regression failure on latest CVS
Previous Message ohp 2005-07-27 09:08:12 Re: regression failure on latest CVS