Pruning useless tables for queries
От | Martijn van Oosterhout |
---|---|
Тема | Pruning useless tables for queries |
Дата | |
Msg-id | 20030521142144.GB15143@svana.org обсуждение исходный текст |
Ответы |
Re: Pruning useless tables for queries
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
[Please CC any responses] One optimisation is for the query planner to drop tables whose output do not affect the final result (where the WHERE clauses and the CHECK constraints prove that no rows can be returned). While this is not the case for simple queries, when involving views and inheritance it's very easy to do. One problem is that you cannot totally remove the table as while the data may not be used, meta-data such as field names and types are still relevant (except in the case of inherited tables). The simple solution would be to introduce a dummy Limit node with zero rows. Ideally, you could create a new node would has a RangeTable (I think that's the right term) but produces no output. This would allow it to be detected by join types and simplified away. It could also be used in the following case: kleptog=# explain select * from website where false; QUERY PLAN ---------------------------------------------------------------Result (cost=0.00..1.28 rows=28 width=63) One-Time Filter:false -> Seq Scan on website (cost=0.00..1.28 rows=28 width=63) (3 rows) I believe the way to approach it would be to add a test to path/allpaths.c:set_plain_rel_pathlist(). Get a list of all the CHECK constraints and then use code similar to path/indxpath.c:pred_test() to check if they are incompatible. It needs to be slightly different as indeterminate should be considered as true rather than false as is the case for partial indexes where the code is currently for. Note, pred_test type code can also be used to remove extraneous clauses from the restrictions, which may improve planning estimates. After this it depends on how this will be represented. If it's a new node, code will have to be added to many places to deal with this. The function set_inherited_rel_pathlist can optimise the subtables away without effecting the rest of the query planner. Initially it may be possible to do it only for inherited tables. Removing tables based on constant restrictions (always true or false) could also be dealt with. One issue is NULLs. In theory IS NULL nodes could be compared against the NOT NULL status of the column, though it's not clear whether this would be easy. From the point of view of pred_test, it fairly straight forward; if the issue is indeterminate, just return and get the current behaviour. Looks pretty simple. What are the chances of getting this accepted? Anyone see any issues I missed? Hace a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organised violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
В списке pgsql-hackers по дате отправления:
Следующее
От: "scott.marlowe"Дата:
Сообщение: Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON