launchpad-dev team mailing list archive

[Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx>]

On Wed, Feb 9, 2011 at 7:12 AM, Robert Collins
<robertc@xxxxxxxxxxxxxxxxx> 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 <stuart@xxxxxxxxxxxxxxxx>
http://www.stuartbishop.net/