Absurdly long queries with Postgres 11 during unit tests

[Rich Rauenzahn]

This is heads up in case anyone sees something similar:

I have managed to trigger this degenerate query case in two completely different Django 2.2 projects.   In production with a normal sized dataset, the query time is fine.  But during unit testing with a small subset of the data, the queries took a long time.  A LONG time.

In the most recent case each query took 100x longer.  200+ seconds instead of 2 seconds.  The unit test dataset isn't very large because it's a unit test.  

I think I may have first seen this when I upgraded the project to postgres 11.

Manually vacuuming between tests resolves the issue.  (Yes, autovacuum is on by default -- and isn't the db created from scratch for each 'manage test' invocation?)

This is how I did it:

def _vacuum():

    # Some unit test queries seem to take a much longer time.

    # Let's try vacuuming.

    # https://stackoverflow.com/a/13955271/2077386

    with connection.cursor() as cursor:

        logger.info("Vacuum: begin")

        cursor.execute("VACUUM ANALYZE")

        logger.info("Vacuum: complete")

class VacuumMixin:

    @classmethod

    def setUpClass(cls):

        _vacuum()

        return super().setUpClass()

    @classmethod

    def tearDownClass(cls):

        ret = super().tearDownClass()

        _vacuum()

        return ret

If anyone else sees this, please let me know.  Maybe we can further RCA it.

[Chetan Ganji]

This might help shed more light on the problem. 

1. https://pypi.org/project/pytest-django-queries/
2. Try cProfile 
   https://www.geeksforgeeks.org/profiling-in-python/

Regards,

Chetan Ganji

+91-900-483-4183

ganji....@gmail.com

http://ryucoder.in

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/e76b105e-ed53-4031-869c-830f94677ef4n%40googlegroups.com.