django-orm-optimization-story-on-selecting-the-least-possible.py

import os
from django.core.wsgi import get_wsgi_application

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "songsearch.settings")

application = get_wsgi_application()

from songsearch.main.models import *

import hashlib


def f1(a):
    names = []
    for song in Song.objects.filter(artist=a):
        names.append(song.name)
    return hashlib.md5("".join(names).encode("utf-8")).hexdigest()


def f2(a):
    names = []
    for song in Song.objects.filter(artist=a).only("name"):
        names.append(song.name)
    return hashlib.md5("".join(names).encode("utf-8")).hexdigest()


def f3(a):
    names = []
    for song in Song.objects.filter(artist=a).values("name"):
        names.append(song["name"])
    return hashlib.md5("".join(names).encode("utf-8")).hexdigest()


def f4(a):
    names = []
    for name in Song.objects.filter(artist=a).values_list("name", flat=True):
        names.append(name)
    return hashlib.md5("".join(names).encode("utf-8")).hexdigest()


from django.contrib.postgres.aggregates import StringAgg
from django.db.models import Func


def f5(a):
    # https://www.peterbe.com/plog/django-orm-optimization-story-on-selecting-the-least-possible#c5043c8
    return Song.objects.filter(artist=a).aggregate(
        names_hash=Func(StringAgg("name", ""), function="md5")
    )


def f6(a):
    # https://www.peterbe.com/plog/django-orm-optimization-story-on-selecting-the-least-possible#c5043c8
    return hashlib.md5(
        Song.objects.filter(artist=a)
        .aggregate(names_hash=StringAgg("name", ""))["names_hash"]
        .encode("utf-8")
    ).hexdigest()


artist = Artist.objects.get(name="Bad Religion")
print(Song.objects.filter(artist=artist).count())
print(f1(artist) == f2(artist))
print(f2(artist) == f3(artist))
print(f3(artist) == f4(artist))
print(f4(artist) == f5(artist))
print(f5(artist) == f6(artist))

# Reporting
import time
import random
import statistics

functions = f1, f2, f3, f4, f5, f6
times = {f.__name__: [] for f in functions}

for i in range(500):
    func = random.choice(functions)
    t0 = time.time()
    func(artist)
    t1 = time.time()
    times[func.__name__].append((t1 - t0) * 1000)

for name in sorted(times):
    numbers = times[name]
    print("FUNCTION:", name, "Used", len(numbers), "times")
    print("\tBEST", min(numbers))
    print("\tMEDIAN", statistics.median(numbers))
    print("\tMEAN  ", statistics.mean(numbers))
    print("\tSTDEV ", statistics.stdev(numbers))

Output I get as of Feb 25 2019:

276
True
True
True
False
False
FUNCTION: f1 Used 92 times
	BEST 5.928993225097656
	MEDIAN 7.311463356018066
	MEAN   7.594626882801885
	STDEV  2.2027017044658423
FUNCTION: f2 Used 75 times
	BEST 2.878904342651367
	MEDIAN 3.3979415893554688
	MEAN   3.4774907430013022
	STDEV  0.5120246550765524
FUNCTION: f3 Used 88 times
	BEST 0.9310245513916016
	MEDIAN 1.1944770812988281
	MEAN   1.3105544176968662
	STDEV  0.35922655625999383
FUNCTION: f4 Used 71 times
	BEST 0.7879734039306641
	MEDIAN 1.1661052703857422
	MEAN   1.2262606284987758
	STDEV  0.3561764250427344
FUNCTION: f5 Used 90 times
	BEST 0.7929801940917969
	MEDIAN 1.0334253311157227
	MEAN   1.1836051940917969
	STDEV  0.4001442703048186
FUNCTION: f6 Used 84 times
	BEST 0.80108642578125
	MEDIAN 1.1119842529296875
	MEAN   1.2281338373819988
	STDEV  0.37146893005516973