Issue: 2.4.5 increased our query count in places we were using aggregate_rows()
Author: jturmel
State: closed
Created: 2015-01-16 15:43:40

In our code we have asserts for query counts to make sure they don't get out of hand by us doing something stupid or Peewee changing in an unexpected way. Once we upgraded from 2.4.4 to 2.4.5 several of our queries with JOINS that were doing one query regressed back to N+1 queries.


And by "in our code", I meant "in our tests"


Interesting -- can you share any of the code or a representative example?


I think perhaps 4c38abd5ea and #482 might be related.


```
class Item(DateTimeModel, LocalizedModel, BaseModel):
    name_en = CharField()
    name_es = CharField(null=True)
    description_en = TextField(null=True)
    description_es = TextField(null=True)
    disclaimer_en = CharField(null=True)
    disclaimer_es = CharField(null=True)
    image_url = CharField(null=True)
    labels = JSONArrayField(null=True)
    status = CharField(choices=constants.STATUSES_FULL)
    style = ForeignKeyField(rel_model=Style)

class Category(DateTimeModel, LocalizedModel, BaseModel):
    parent = ForeignKeyField(rel_model='self', null=True)
    name_en = CharField()
    name_es = CharField(null=True)
    description_en = TextField(null=True)
    description_es = TextField(null=True)
    disclaimer_en = CharField(null=True)
    disclaimer_es = CharField(null=True)
    image_url = CharField(null=True)
    start_at = DateTimeField(null=True)
    end_at = DateTimeField(null=True)
    sort_order = FloatField(default=0)
    is_featured = BooleanField(default=False)
    status = CharField(choices=constants.STATUSES_FULL)

class CategoryItem(DateTimeModel, BaseModel):
    category = ForeignKeyField(rel_model=Category, on_delete='CASCADE', related_name="items")
    item = ForeignKeyField(rel_model=Item, on_delete='CASCADE', related_name="categories")
    sort_order = FloatField(default=0)

    class Meta:
        db_table = 'CategoryItem'
        primary_key = CompositeKey('category', 'item')

class StyleCategory(DateTimeModel, BaseModel):
    name = CharField()

class Style(DateTimeModel, BaseModel):
    id = IntegerField(primary_key=True, default=0)
    name = CharField()
    comments = TextField(null=True)
    status = CharField(choices=constants.STATUSES_FULL)
    category = ForeignKeyField(rel_model=StyleCategory)

class StyleVariant(DateTimeModel, LocalizedModel, BaseModel):
    id = IntegerField(primary_key=True, default=0)
    style = ForeignKeyField(rel_model=Style, on_delete='CASCADE', related_name='variants')
    name_en = CharField()
    name_es = CharField(null=True)

items = (
    sql.Item.select(
        sql.Item, sql.CategoryItem, sql.Category, sql.Style, sql.StyleVariant)
    .join(sql.CategoryItem, JOIN_LEFT_OUTER)
    .join(sql.Category, JOIN_LEFT_OUTER)
    .switch(sql.Item)
    .join(sql.Style)
    .join(sql.StyleVariant, JOIN_LEFT_OUTER)
)

where = []
where.append(sql.Category.id == params[‘category_id'])
items = items.order_by(sql.CategoryItem.sort_order.asc())

if where:
    items = items.where(reduce(operator.and_, where))

items = list(items.aggregate_rows())
```


I was hoping for something a little more compact..lol. But I think I've identified the issue and am working on tests/fixes.


@jturmel -- I see that in your query you are doing:

``` python
    .switch(sql.Item)
    .join(sql.Style)
```

But I don't see a foreign key between Item and Style. How does this work?


Sorry, was abstracting some of the business specifics...

Item has (updated post above to reflect this):
        style = ForeignKeyField(rel_model=Style)


I've got to be frank, your code you pasted does absolutely nothing to help me :)


No problem, too little context:

```
class Item(DateTimeModel, LocalizedModel, BaseModel):
    name_en = CharField()
    name_es = CharField(null=True)
    description_en = TextField(null=True)
    description_es = TextField(null=True)
    disclaimer_en = CharField(null=True)
    disclaimer_es = CharField(null=True)
    image_url = CharField(null=True)
    labels = JSONArrayField(null=True)
    status = CharField(choices=constants.STATUSES_FULL)
    style = ForeignKeyField(rel_model=Style)
```


No, I mean the whole block. It's massive with all sorts of extraneous fields. I don't know why it's creating the query dynamically, nor do I understand what the expected results are.


@jturmel -- can you produce a _minimal_ example query that replicates the problem? I'm working on it on my end, but am having trouble replicating.


Okay, here's a standalone test that uses Sqlite so should be easy to just run on your end.

https://gist.github.com/jturmel/64f7527c4f225b591494

Run under 2.4.4 = 1 query
Run under 2.4.5 = 5 queries


I'm running this with current peewee master and the test script runs without problems.


Also thank you very much for distilling this down into a small example. I really appreciate it!


EDIT: Actually nevermind, I get the error now. My bad.


The plot thickens... I checked out version 2.4.4 and now instead of 5 queries I am getting 3:

``` sql
('SELECT "t1"."id", "t1"."name", "t1"."style_id", "t2"."category_id", "t2"."item_id", "t2"."sort_order", "t3"."id", "t3"."parent_id", "t3"."sort_order", "t4"."id", "t4"."category_id", "t5"."id", "t5"."style_id" FROM "item" AS t1 LEFT OUTER JOIN "categoryitem" AS t2 ON ("t1"."id" = "t2"."item_id") INNER JOIN "style" AS t4 ON ("t1"."style_id" = "t4"."id") LEFT OUTER JOIN "stylevariant" AS t5 ON ("t5"."style_id" = "t4"."id") LEFT OUTER JOIN "category" AS t3 ON ("t2"."category_id" = "t3"."id") WHERE ("t3"."id" = ?) ORDER BY "t2"."sort_order" ASC', [1])
('SELECT "t1"."id", "t1"."parent_id", "t1"."sort_order" FROM "category" AS t1 WHERE ("t1"."id" = ?) LIMIT 1', [1])
('SELECT "t1"."id", "t1"."name", "t1"."style_id" FROM "item" AS t1 WHERE ("t1"."id" = ?) LIMIT 1', [1])
```

I made some tweaks to the current version of Python and I am able to get 3 queries, but neither under 2.4.4 or 2.4.5 do I get just one.

The issue revolves around the way the CompositeKey interacts with the aggregate result wrapper. If I remove the `primary_key = CompositeKey(...)` line then I get a single query.

Is it possible that you all changed that in your code and that's what is causing the test failure?


We just upgraded from 2.4.4 to 2.4.5 to get the latest improvements/fixes
for other things and notices this regression and messed with it a bit to
try and figure out what was going on but without all the domain knowledge
it's difficult to dive in so we just ended up rolling back to 2.4.4.

Not sure why it's giving you 3 vs 1 (vs 5) -- the composite key is to
enforce one of association of item to category (unique key)... any other
thoughts/ideas?

On Sat, Jan 17, 2015 at 8:59 PM, Charles Leifer notifications@github.com
wrote:

> The plot thickens... I checked out version 2.4.4 and now instead of 5
> queries I am getting 3:
> 
> ('SELECT "t1"."id", "t1"."name", "t1"."style_id", "t2"."category_id", "t2"."item_id", "t2"."sort_order", "t3"."id", "t3"."parent_id", "t3"."sort_order", "t4"."id", "t4"."category_id", "t5"."id", "t5"."style_id" FROM "item" AS t1 LEFT OUTER JOIN "categoryitem" AS t2 ON ("t1"."id" = "t2"."item_id") INNER JOIN "style" AS t4 ON ("t1"."style_id" = "t4"."id") LEFT OUTER JOIN "stylevariant" AS t5 ON ("t5"."style_id" = "t4"."id") LEFT OUTER JOIN "category" AS t3 ON ("t2"."category_id" = "t3"."id") WHERE ("t3"."id" = ?) ORDER BY "t2"."sort_order" ASC', [1])
> ('SELECT "t1"."id", "t1"."parent_id", "t1"."sort_order" FROM "category" AS t1 WHERE ("t1"."id" = ?) LIMIT 1', [1])
> ('SELECT "t1"."id", "t1"."name", "t1"."style_id" FROM "item" AS t1 WHERE ("t1"."id" = ?) LIMIT 1', [1])
> 
> I made some tweaks to the current version of Python and I am able to get 3
> queries, but neither under 2.4.4 or 2.4.5 do I get just one.
> 
> The issue revolves around the way the CompositeKey interacts with the
> aggregate result wrapper. If I remove the primary_key = CompositeKey(...)
> line then I get a single query.
> 
> Is it possible that you all changed that in your code and that's what is
> causing the test failure?
> 
> —
> Reply to this email directly or view it on GitHub
> https://github.com/coleifer/peewee/issues/503#issuecomment-70394533.


The problem had to do with the composite primary key. When building the identity map, peewee asks for the primary keys of the constructed instances (to map them back later when constructing the model graph). The problem was that getting the PK value of a composite key field was resolving the foreign keys, triggering 2 extra queries per iteration (one for the category and one for the item). Since there's two iterations, 4 extra queries.

I fixed this by forcing peewee to use the raw field data when mapping composite keys, avoiding the extra lookups.


Great!

FTR, just now I created an entirely new Python env, installed nose and peewee 2.4.4 and was able to see the 3 vs 5 query (as opposed to 1 vs 5 I was seeing in our site's environment) -- that said it's probably not worth figuring out the difference there as it looks like you've figured it out.

Think there should be tests like what I sent as a case so there aren't future regressions on this specifically?


Yeah, see 6fb024372cc77ccae47fb04624840bbee1a84df6