GIN index 테스트

테스트 절차

모델 및 인덱스 생성

각각의 인덱스로 조회 및 시간 측정

모델 row 수를 100000, 1000000, 2000000, 3000000, …, 10000000으로 측정

class Test(Model):
    class Meta:
        indexes = [
            GinIndex(fields=['search_vector']),
            Index(fields=['username'])
        ]
        app_label = 'test'

    search_vector = SearchVectorField(blank=True, null=True)
    search_text = TextField(blank=True, null=True)
    username = CharField(max_length=150, blank=True, null=True)
    human_name = CharField(max_length=100, blank=True, null=True)

def test_index(data_cnt):
    from .models import TemporaryInformation
    from uuid import uuid4
    total_cnt = Test.objects.all().count()
    iter_cnt = data_cnt - total_cnt
    name = ''
    for _ in range(iter_cnt//10):
        name = str(uuid4())
        for __ in range(10):
            tmp = Test(
                username=name
            )
            tmp.save()
    import time
    time.sleep(30)
    start = time.time()
    print(Test.objects.filter(search_vector=name)[0].username)
    end = time.time()
    delta = end - start
    print(f'search vector: {delta} s')

    start1 = time.time()
    print(Test.objects.filter(username=name)[0].username)
    end2 = time.time()
    delta2 = end2 - start1
    print(f'normal index: {delta2} s')

    print(f'diff: {delta - delta2}')

    print(f'rate: {(delta / delta2)}')

def test_index_cnt(cnt):
    name = '113e8729-db91-4e70-a1b9-fca19c7292cf'
    import time
    from .models import Test
    for idx in cnt:
        print(f'{idx} 회차')
        print('----------------')
        start = time.time()
        print(Test.objects.filter(search_vector=name)[0].username)
        end = time.time()
        delta = end - start
        print(f'search vector: {delta} s')

        start1 = time.time()
        print(Test.objects.filter(username=name)[0].username)
        end2 = time.time()
        delta2 = end2 - start1
        print(f'normal index: {delta2} s')

테스트 결과

row 수 별 검색 소요 시간

	GIN Index(search vector)	Normal Index(index range scan)
100000	0.012472152709960938	0.003549337387084961
1000000	0.00644993782043457	0.0003342628479003906
2000000	0.0017161369323730469	0.00032210350036621094
3000000	0.00834202766418457	0.0003218650817871094
4000000	0.018002033233642578	0.016844987869262695
5000000	0.007339000701904297	0.00039124488830566406
6000000	0.011085271835327148	0.0004980564117431641
7000000	0.0034601688385009766	0.00038313865661621094
8000000	0.0024018287658691406	0.00039887428283691406
9000000	0.03994297981262207	0.0012121200561523438
10000000	0.0406489372253418	0.004667043685913086

10000000건 반복 조회 테스트

	GIN Index(search vector)	Normal Index(index range scan)
1회차	0.0385589599609375	0.0006618499755859375
2회차	0.008337974548339844	0.0004711151123046875
3회차	0.007966041564941406	0.0004279613494873047
4회차	0.00814199447631836	0.00046706199645996094
5회차	0.007985115051269531	0.00046706199645996094
6회차	0.007925033569335938	0.00042819976806640625
7회차	0.007919788360595703	0.0004220008850097656
8회차	0.008038997650146484	0.0004839897155761719
9회차	0.00966191291809082	0.0004928112030029297
10회차	0.008493900299072266	0.0004630088806152344
11회차	0.007897138595581055	0.00041294097900390625
12회차	0.008209228515625	0.0004851818084716797
13회차	0.007968902587890625	0.0004379749298095703
14회차	0.007870912551879883	0.0004029273986816406
15회차	0.007960081100463867	0.00041604042053222656
16회차	0.007874011993408203	0.0004038810729980469
17회차	0.008240699768066406	0.0004930496215820312
18회차	0.00803518295288086	0.00043773651123046875
19회차	0.007933855056762695	0.00045680999755859375
20회차	0.008169889450073242	0.0004410743713378906
21회차	0.00801396369934082	0.0004439353942871094
22회차	0.00800776481628418	0.0004322528839111328
23회차	0.007935285568237305	0.0004169940948486328
24회차	0.007859945297241211	0.00041222572326660156
25회차	0.007918119430541992	0.0004439353942871094
26회차	0.008023977279663086	0.0005030632019042969
27회차	0.008026123046875	0.0005171298980712891
28회차	0.008137941360473633	0.0005068778991699219
29회차	0.007944107055664062	0.0004367828369140625
30회차	0.007898092269897461	0.0004248619079589844