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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
