On Wed, Feb 9, 2011 at 7:12 AM, Robert Collins <[email protected]> wrote:
> Because of this, when dealing with two separate but highly selective > indices on joined tables, it can be *faster* to do two queries and > union them, than to do a single query with an OR clause: in principle > the query planner can figure this all out, but in practice - well in > practice we need to measure /everything/ we do :). So when we see a > UNION query, don't assume its a bad query builder artifact - it may > be, *or* it may be a solution to querying quickly with our schema. bug > 714383 has details on a case where we should add a union in bug search > because of this. I agree with Julian below that we need to be better at documenting why queries are being done a particular way, and also when. Queries have been put together in odd ways due to the way the code assembles the query, due to limitations in SQLObject, lack of coffee, and because they are faster that way - or *where* faster, because a query optimized for PostgreSQL 8.1 with 4 year old data is probably no longer optimized. > For the same root causes - constraining the intermediary tables that > are created, and the amount of data being processed, I'm growing more > and more concerned about the impact of wide queries - 'prejoins' on > our system; I'm starting to think we should have a default of set > based eager loading rather than wide-query eager loading : what do you > think? I really hadn't considered the overhead of redundantly deserializing the same object repeatedly, but agree it is a problem caused by aggressive prejoining on large result sets. I suspect it isn't a problem for small result sets, so 10 tables of prejoins on a query returning 20 rows is likely faster than a 20 row query followed by 10 single row queries. Of course, we don't see the queries that are performing well when we are looking at OOPS reports :) -- Stuart Bishop <[email protected]> http://www.stuartbishop.net/ _______________________________________________ Mailing list: https://launchpad.net/~launchpad-dev Post to : [email protected] Unsubscribe : https://launchpad.net/~launchpad-dev More help : https://help.launchpad.net/ListHelp
