BIND 10 #1899: performance issue of SQLite3 "iterate" query (w/ or w/o NSEC3)

[BIND 10 Development]

#1899: performance issue of SQLite3 "iterate" query (w/ or w/o NSEC3)
-------------------------------------+-------------------------------------
            Reporter:  jinmei        |                        Owner:
                Type:  defect        |                       Status:  new
            Priority:  medium        |                    Milestone:  New
           Component:  data source   |  Tasks
           Sensitive:  0             |                     Keywords:
         Sub-Project:  DNS           |              Defect Severity:  N/A
Estimated Difficulty:  0             |  Feature Depending on Ticket:
         Total Hours:  0             |          Add Hours to Ticket:  0
                                     |                    Internal?:  0
-------------------------------------+-------------------------------------
 Our current implementation of iterating a zone in an SQLite3 data
 source isn't scalable and won't work well for a huge zone (e.g.,
 when enumerating the zone's RRs for xfrout).  It was already so
 before #1782.  See the very end of
 https://lists.isc.org/pipermail/bind10-dev/2012-March/003167.html

 But I suspect #1782 has made it worse due to the introduction of union:

 {{{
 SELECT rdtype, ttl, sigtype, rdata, name, rname FROM records
 WHERE zone_id = ?1
 UNION
 SELECT rdtype, ttl, "NSEC3", rdata, owner, owner FROM nsec3
 WHERE zone_id = ?1 ORDER by rname, rdtype
 }}}

 Even if we define optimized indices for both normal and nsec3
 tables, the union will require generating temporary B-tree and will
 make the iteration unacceptably slow:

 {{{
 sqlite> explain query plan SELECT rdtype, ttl, sigtype, rdata, name, rname
 FROM records WHERE zone_id = 1 union select rdtype, ttl, "NSEC3", rdata,
 owner, owner FROM nsec3 WHERE zone_id = 1 limit 10 ;
 1|0|0|SCAN TABLE records (~100000 rows)
 2|0|0|SCAN TABLE nsec3 (~100000 rows)
 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
 }}}

 There seem to be several choices:

 - Avoid sorting and use 'UNION ALL' (the latter would make sense
   anyway, as long as we use UNION).  Personally, I'd still like to
   keep the result at least sorted by names, however.  (I know there
   are discussions about whether we need to ensure the iteration result
   is sorted)
 - Use two separate queries for these tables and return the combined
   result via the iterator object.
 - Maybe some other way

-- 
Ticket URL: <http://bind10.isc.org/ticket/1899>
BIND 10 Development <http://bind10.isc.org>
BIND 10 Development