Russ Brown wrote: > On Wed, 2006-09-13 at 12:00 -0700, Matt Good wrote: >> Russ Brown wrote: >>> Yesterday I upgraded our trac backend to postgres, and afterwards >>> decided to do a bit of query analyzing. >>> >>> One that hung around quite a lot in pg_stat_activity was this one from >>> SubversionRepository.get_youngest_rev_in_cache, which is called from the >>> timeline: >>> >>> SELECT rev FROM revision ORDER BY -LENGTH(rev), rev DESC LIMIT 1; >>> >>> The problem is this query is never going to use the index on rev, which >>> in our case means a sequential scan over all 28000+ revision rows. >>> >>> I tried but failed to improve it without a change to both the database >>> and code, but did manage to improve it without having to change the >>> table definition. >>> >>> First, create a functional index on a cast of rev to integer: >>> >>> CREATE INDEX revision_rev_int_idx ON revision (CAST(rev AS integer)); >> >> While SVN only uses integer values for revisions we're trying to make >> Trac compatible with other version control systems which may not use >> numeric identifiers. Most distributed version control systems are >> patch-based and use hashes rather than integers at patch identifiers, >> since there's no way to guarantee a unique sequence when you're working >> with patches distributed across many repositories. So, unfortunately >> casting the revision as an integer will not work for other version >> control systems. >> > > True, except that the query in question appears in > versioncontrol/svn_fs.py, so unless the file is badly named it's > subversion-specific code making it fair-game for the casting.
I had a similar problem during devlopment of the PerforcePlugin where the call to get_youngest_rev_in_cache() was adding a 1-2 second overhead on large Perforce repositories. I ended up working around the problem by indexing on the 'time' field instead of the 'rev' field, however this has introduced its own problems (see http://trac-hacks.org/ticket/659#comment:3). It would be much better to index using a numerical comparison on the 'rev' field as mentioned above, but from my cursory reading of the documentation I don't think this type of index is supported in SQLite. Is there another more portable way to achieve this numerical sort for version control system plugins that can safely assume integer revisions? -- Lewis Baker I-SiTE Software Engineer 3D Laser Imaging Email : [EMAIL PROTECTED] http://www.isite3d.com.au/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to trac-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/trac-users -~----------~----~----~----~------~----~------~--~---
begin:vcard fn:Lewis Baker n:Baker;Lewis org:I-SiTE 3D Laser Imaging;Software Development Team adr:;;63 Conyngham St.;Glenside;SA;5065;Australia email;internet:[EMAIL PROTECTED] title:Software Engineer tel;work:+61 8 8338 9222 note:OpenPGP Key ID: 0x60D0F558 x-mozilla-html:TRUE url:http://www.isite3d.com version:2.1 end:vcard
