On Wed, Feb 02, 2011 at 04:45:16PM -0800, Jeff Rogers wrote: > Nicolas Williams wrote: > >On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote: > > > >>SQLite seems to do quite poorly performance-wise with fully-normalized > >>attribute tables like this, when you want to query against multiple > >>attributes. My timing comparisons with postgres show sqlite to be as > >>much as 10x-15x slower than pg. > >> > >>My timing code is at http://paste.tclers.tk/2346 > > > >You need an index on props(id) -- you always need an index on columns > >that form a foreign key. SQLite3 requires that if you want to cascade > >deletions. > > Ok, that caught me by surprise, but it improved things a lot. With > that index sqlite is now significantly faster than pg on 3 of the > tests, but still similar to or slower than pg on the remaining 2 - > in my code, "sx" and "sx3". (I can't be sure of the exact timings > because I can't run sqlite and the pg server on the same machine) > > Any idea why pg does ok on these queries without the extra index - > Maybe they're created by default? SQLIte doesn't create any indexes > automatically on primary key fields or anything else, correct?
No, it doesn't. Use EXPLAIN QUERY PLAN to see what SQLite3 is doing. Also, see the followup about covering indexes. Also, run ANALYZE. Finally, you might denormalize somewhat by having a trigger to copy the obj.name column to the props table and add that column to the _end_ of the covering indexes. Then SQLite3 might be able to do your joins using a single index. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
