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?
Regards,
Dawid
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
