[
https://jira.nuxeo.com/browse/NXP-7847?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Benoit Delbosc updated NXP-7847:
--------------------------------
Status: Open (was: Triage)
> 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
> Assignee: 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
