Minor There are few SQL requests like:
SELECT hierarchy_id FROM ancestors WHERE ancestors IS NULL; SELECT id FROM hierarchy_read_acl WHERE acl_id IS NULL;
They generate sequential scan because index are not used for the NULL value. This can be
fixed by adding partial index on PostgreSQL for instance.
nuxeo=# EXPLAIN ANALYZE SELECT id FROM ancestors WHERE ancestors IS NULL;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on ancestors (cost=0.00..2275.76 rows=1 width=37) (actual time=11.311..11.470 rows=1 loops=1)
Filter: (ancestors IS NULL)
Total runtime: 11.500 ms
(3 rows)
Time: 12.034 ms
nuxeo=# CREATE INDEX ancestors_ancestors_is_null_idx ON ancestors USING btree(ancestors) WHERE ancestors IS NULL;
CREATE INDEX
Time: 19.981 ms
nuxeo=# EXPLAIN ANALYZE SELECT id FROM ancestors WHERE ancestors IS NULL;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ancestors_ancestors_is_null_idx on ancestors (cost=0.00..8.27 rows=1 width=37) (actual time=0.046..0.047 rows=1 loops=1)
Index Cond: (ancestors IS NULL)
Total runtime: 0.070 ms
(3 rows)
It's 160x faster with a partial index.
For Oracle this can also be done with an index, but it does not work with nested table.
http://www.dba-oracle.com/oracle_tips_null_idx.htm
