Views, joins and LIMIT
От | Dawid Kuroczko |
---|---|
Тема | Views, joins and LIMIT |
Дата | |
Msg-id | 758d5e7f04101102543eab8dca@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Views, joins and LIMIT
|
Список | pgsql-performance |
I've been wondering... Suppose we have two tables CREATE TABLE messages ( message_id serial PRIMARY KEY, message text NOT NULL ); CREATE TABLE entries ( entry_id serial PRIMARY KEY, message_id integer NOT NULL REFERENCES messages ); And we have a join: SELECT entry_id,message FROM entries NATURAL JOIN messages ORDER BY entry_id DESC LIMIT 10; The typical planners order of doing things is -- join the tables, perform sort, perform limit. But in the above case (which I guess is quite common) few things can be assumed. 1) to perform ORDER BY we don't need any join (entry_id is in our entries table). 2) entries.entry_id references PRIMARY KEY, which is unique, so we will have not less, not more but exactly one row per join (one row from messages per one row from entries) 3) Knowing above, instead of performing join on each of thousands of entries rows, we could perform ORDER BY and LIMIT before JOINing. 4) And then, after LIMITing we could JOIN those 5 rows. This I guess would be quite benefitial for VIEWs. :) Other thing that would be, I guess, benefitial for views would be special handling of lines like this: SELECT entry_id,message_id FROM entries NATURAL JOIN messages; Here there is no reason to perform JOIN at all -- the data will not be used. As above, since entries.message_id IS NOT NULL REFERENCES messages and messages is UNIQUE (PRIMARY KEY) we are sure there will be one-to-one(*) mapping between two tables. And since these keys are not used, no need to waste time and perform JOIN. I wonder what you all think about it. :) Regards, Dawid (*) not exactly one-to-one, because same messages.message_id can be references many times from entries.message_id, but the join will return exactly the same number of lines as would select * from entries;
В списке pgsql-performance по дате отправления: