vipyrsec / dragonfly-mainframe

The mainframe for Dragonfly
https://docs.vipyrsec.com/dragonfly-mainframe/
MIT License
4 stars 2 forks source link

Add index to `download_urls.scan_id` #170

Closed jonathan-d-zhang closed 9 months ago

jonathan-d-zhang commented 9 months ago

Adds an index to the scans id column. This speeds up queries by ~182x. Testing with:

#!/bin/bash
pgbench -n -f query.sql -T120 -P24 -c5 -j5 \
    postgresql://postgres:postgres@localhost:5432/dragonfly

Existing

number of transactions actually processed: 28991
latency average = 20.694 ms
latency stddev = 4.759 ms
tps = 241.591310 (without initial connection time)

New

number of transactions actually processed: 5264042
latency average = 0.114 ms
latency stddev = 0.023 ms
tps = 43874.227030 (without initial connection time)