vipyrsec / dragonfly-mainframe

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

Add index to `scans.status` #171

Closed jonathan-d-zhang closed 9 months ago

jonathan-d-zhang commented 9 months ago

Adds an index to the scans.status column. This speeds up queries by ~305x. 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: 13585
latency average = 44.165 ms
latency stddev = 0.987 ms
tps = 113.209099 (without initial connection time)

New

number of transactions actually processed: 4140845
latency average = 0.145 ms
latency stddev = 0.025 ms
tps = 34516.420929 (without initial connection time)