query takes too long to complete

[keinagae]

Hi this my reflected table

class AmsPortfolio(edgy.ReflectModel):
    id = edgy.IntegerField(null=False, primary_key=True)
    updated_at = edgy.DateTimeField(null=False, auto_now=True)
    created_at = edgy.DateTimeField(auto_now_add=True,null=False)
    portfolio_name = edgy.CharField(max_length=255, null=False)
    country_code = edgy.CharField(max_length=30, null=False)
    currency = edgy.CharField(max_length=3, null=False)
    company_type = edgy.SmallIntegerField(null=False)
    channel_id = edgy.ForeignKey(null=False, to=ChannelChannel, on_update=None, on_delete=edgy.CASCADE, related_name='ads_portfolios')
    company_id = edgy.ForeignKey(null=False, to=PortalCompany, on_update=None, on_delete=edgy.CASCADE, related_name='ads_portfolios')
    channel_portfolio_id = edgy.CharField(max_length=255, null=True)
    country_id = edgy.ForeignKey(null=False, to=PortalCountry, on_update=None, on_delete=edgy.CASCADE, related_name='ads_portfolios')
    portfolio_id = edgy.CharField(max_length=50, null=True)

    class Meta:
        registry = registry
        tablename = 'AMSPortfolio'
        unique_together = [UniqueConstraint(fields=['portfolio_name', 'company_id', 'country_id', 'country_code', 'currency', 'company_type', 'channel_id'], name='AMSPortfolio_portfolio_name_company_i_9cb1d635_uniq')]
        indexes = [Index(suffix='idx', name='AMSPortfolio_country_id_c976b1c5', fields=['country_id']), Index(suffix='idx', name='AMSPortfolio_channel_id_11ccb347', fields=['channel_id']), Index(suffix='idx', name='AMSPortfolio_company_id_38362622', fields=['company_id'])]

and when i run a simple query like await AmsPortfolio.query.filter(company_id='<id>',).all() its takes ages to complete initially i though it was database that was slow so it tried with await database.execute("""select * from "AMSPortfolio" where company_id='<id>'""") it takes lees then a second and my table has only around 2500 records so any idea what could be wrong here?

[tarsil]

@devkral can we have a look, please? Thank you @keinagae we will investigate, recently we have been refactor some internals and replacing some of the legacies we had but we will check this.

[tarsil]

@keinagae for our understanding. Are you using the latest versions of the edgy and databasez?

[devkral]

What version of databasez you use?

[devkral]

sry was occupied with a feature+sick

[devkral]

do you use threading or select_related?

Anyway: we have real iterations now. As a remedy you could use the async iterate feature to sift through the records. It should be way more performant. It would be also nice to know if iterating is way faster and I introduced a bug in awaiting an array.

[devkral]

Note: databasez is a library heavily used by edgy. I changed there a lot so it would be helpful to know the version.

[devkral]

fixed an performance issue, which caused a double load when accessing non-available attributes.
Is the problem fixed with 0.25.0?