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