[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
