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
somenamesfor 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
(primarykey), 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?
Regards, Dawid