launchpad-dev team mailing list archive

[Robert Collins <robertc@xxxxxxxxxxxxxxxxx>]

The maintenance squads have been making great progress on timeouts.
Over the last week a couple of interesting things have shown up, which
I think are worth calling attention to.

Firstly, we've indexed 90% of the BugMessage table now. We can move
forward and start doing range queries on that table to make
BugTask:+index massively faster. This is second highest frequency
timeout we have, it will be fantastic to put it to bed.

Secondly, there is some confusion about what makes queries fast in
SQL. Basically the goal has to be to reduce the amount of data being
examined as rapidly as possible - to use the most selective index
possible; our schemas need to be compatible with that goal, or we
can't query efficiently.

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.

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?

-Rob