Improve SQL queries with IS NULL clause
---------------------------------------
Key: NXP-7847
URL: https://jira.nuxeo.com/browse/NXP-7847
Project: Nuxeo Enterprise Platform
Issue Type: Improvement
Components: Core SQL Storage
Affects Versions: 5.4.2
Environment: PostgreSQL and Oracle
Reporter: Benoit Delbosc
There are few SQL requests like:
{code}
SELECT hierarchy_id FROM ancestors WHERE ancestors IS NULL;
SELECT id FROM hierarchy_read_acl WHERE acl_id IS NULL;
{code}
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.
{code}
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)
{code}
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
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
_______________________________________________
ECM-tickets mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
