fix: naive take on adding an index

[drewdelano]

user_id on uploads should probably be indexed...

completes #61

[vasco-santos]

Hey @drewdelano
Can we know more about the motivation for this index? We currently have UNIQUE (user_id, source_cid) for upload table, which already provides an index for user_id behind the scenes. Here you filter out non deleted uploads, but I would say we don't need it unless we are experiencing slower queries with the already existing index.

[drewdelano]

@vasco-santos This is the issue I'm looking at:
#61

web3.storage/packages/db/index.js

Line 437 in 112b178

let query = this._client

We seem to be seeing a steady increase over time which in my mind points to indexing.

This seems related to list all uploads from a given user (that aren't deleted). That said, it might be worth running that query against staging to see how long it takes to return. It's entirely possible I'm wrong here.

[vasco-santos]

Suggested change

	CREATE INDEX IF NOT EXISTS upload_user_id_deleted_at_idx ON upload (user_id) WHERE deleted_at IS NULL;
	CREATE INDEX CONCURRENTLY IF NOT EXISTS upload_user_id_deleted_at_idx ON upload (user_id) WHERE deleted_at IS NULL;

This needs to concurrently so that the operation does not lock the database

[vasco-santos]

That said, it might be worth running that query against staging to see how long it takes to return. It's entirely possible I'm wrong here.

We can definitely try it. Maybe the deleted is the key difference for users with a large number of uploads

[ribasushi]

@vasco-santos @drewdelano why not test all this ( EXPLAIN ANALYZE in hand ) on one of your replicas? The indices are all replicated and identical.

[vasco-santos]

@vasco-santos @drewdelano why not test all this ( EXPLAIN ANALYZE in hand ) on one of your replicas? The indices are all replicated and identical.

Yes, good call @ribasushi . I was thinking about trying that before we move forward here. @drewdelano does not have access so must be me to try it

[vasco-santos]

It looks like this helps a lot! @drewdelano

EXPLAIN ANALYZE 
SELECT * FROM upload
WHERE user_id = XXXXXXXXXXXXXXXXX
AND deleted_at IS NULL;

Before Index

 Bitmap Heap Scan on upload  (cost=6443.97..279801.88 rows=203188 width=279) (actual time=5260.205..68250.982 rows=187339 loops=1)
   Recheck Cond: (user_id = 'XXXXXXXXXXXXXXXXX'::bigint)
   Filter: (deleted_at IS NULL)
   Heap Blocks: exact=131036
   ->  Bitmap Index Scan on upload_user_id_source_cid_key  (cost=0.00..6433.81 rows=203799 width=0) (actual time=5228.591..5228.592 rows=187391 loops=1)
         Index Cond: (user_id = 'XXXXXXXXXXXXXXXXX'::bigint)
 Planning Time: 0.118 ms
 JIT:
   Functions: 4
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.488 ms, Inlining 0.000 ms, Optimization 0.519 ms, Emission 3.815 ms, Total 4.822 ms
 Execution Time: 68301.216 ms
(12 rows)

After Index

 Index Scan using upload_user_id_deleted_at_idx on upload  (cost=0.09..216730.20 rows=202877 width=279) (actual time=0.071..525.612 rows=187339 loops=1)
   Index Cond: (user_id = 'XXXXXXXXXXXXXXXXX'::bigint)
 Planning Time: 3.819 ms
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.194 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 0.194 ms
 Execution Time: 564.808 ms
(8 rows)

[vasco-santos]

Also name number will need to be updated

[vasco-santos]

LGTM