search

dotnetcore / CAP

Distributed transaction solution in micro-service base on eventually consistency, also an eventbus with Outbox pattern
http://cap.dotnetcore.xyz
MIT License
6.61k stars 1.28k forks source link

Add index in received table #1575

Closed maisa92 closed 1 month ago

maisa92 commented 1 month ago

We are experiencing a significant performance issue with the following DELETE query on our PostgreSQL database

DELETE FROM "cap"."received" WHERE "Id" IN (SELECT "Id" FROM "cap"."received" WHERE "ExpiresAt" < $1 AND ("StatusName"='Succeeded' OR "StatusName"='Failed') LIMIT $2)

This query requires a full table scan instead of utilizing an index scan, leading to high I/O and longer execution times.

To improve the query performance, I think adding an index on the ExpiresAt column will be helpful

yang-xiaodong commented 1 month ago

Would you want to make a PR like #426 ?

maisa92 commented 1 month ago

yes, do u have any concerns? at first, we will add indexes on our side, and if we see improvements, I will send u PR.

yang-xiaodong commented 1 month ago

Adding indexes means a decline in write and update performance. Considering that most users may not have large amounts of data (our main focus is on solving transaction consistency issues) and that our write and update operations will be more frequent, we did not build in indexes but instead suggest that users with large data volumes manually add indexes.

This is my initial thought.

maisa92 commented 1 month ago

got it, we will add on our side