Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

chore: query performance in PostgreSQL by avoiding ordering in queries #2902

Closed
Tracked by #3072
Ivansete-status opened this issue Jul 13, 2024 · 1 comment
Closed
Tracked by #3072

chore: query performance in PostgreSQL by avoiding ordering in queries #2902

Ivansete-status opened this issue Jul 13, 2024 · 1 comment
Assignees
@Ivansete-status
Labels
effort/hours Estimated to be completed in a few hours

Comments

@Ivansete-status
Copy link
Collaborator

Background

While analyzing queries' performance, it can be seen that the ordering part of the query induces a relatively high cost.

For example, the following command...

EXPLAIN SELECT messageHash, pubsubTopic, contentTopic, payload, version, timestamp, meta FROM messages  WHERE (timestamp, messageHash) < ('1720302271182353364','45556c0703060705c99b1aef6664016d5e56813d3729fa3da3ee8eb50db90b96') AND contentTopic IN ('/waku/1/0xc95d2429/rfc26','/waku/1/0xab62afd0/rfc26','/waku/1/0xb6368e8a/rfc26','/waku/1/0xdadfdbfa/rfc26','/waku/1/0x89bed93d/rfc26','/waku/1/0xa0a6b41b/rfc26','/waku/1/0xd811cd50/rfc26','my-ctopic-2','/waku/1/0x242ed557/rfc26') AND pubsubTopic = '/waku/2/rs/16/32' AND timestamp >= '1720245600080250079' AND timestamp <= '1720863401735745080' ORDER BY timestamp DESC, messageHash DESC LIMIT '20';

... gives the following results:

image

On the other hand, if we skip the ORDER BY statements, then the following command ...

EXPLAIN SELECT messageHash, pubsubTopic, contentTopic, payload, version, timestamp, meta FROM messages  WHERE (timestamp, messageHash) < ('1720302271182353364','45556c0703060705c99b1aef6664016d5e56813d3729fa3da3ee8eb50db90b96') AND contentTopic IN ('/waku/1/0xc95d2429/rfc26','/waku/1/0xab62afd0/rfc26','/waku/1/0xb6368e8a/rfc26','/waku/1/0xdadfdbfa/rfc26','/waku/1/0x89bed93d/rfc26','/waku/1/0xa0a6b41b/rfc26','/waku/1/0xd811cd50/rfc26','my-ctopic-2','/waku/1/0x242ed557/rfc26') AND pubsubTopic = '/waku/2/rs/16/32' AND timestamp >= '1720245600080250079' AND timestamp <= '1720863401735745080' LIMIT '20';

... gives the following results:

image

I think we can avoid ordering in the database and instead, perform the desired ordering in nwaku code base.
@Ivansete-status Ivansete-status self-assigned this Jul 13, 2024
@chair28980 chair28980 mentioned this issue Jul 13, 2024
Closed
14 tasks
@Ivansete-status Ivansete-status mentioned this issue Jul 17, 2024
Closed
@gabrielmer gabrielmer added the effort/hours Estimated to be completed in a few hours label Jul 22, 2024
@Ivansete-status
Copy link
Collaborator Author

Closing this as per the comment: #2917 (review)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@Ivansete-status Ivansete-status

Labels
effort/hours Estimated to be completed in a few hours
Projects
Waku
Archived in project
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

chore: postgres_driver - reduce query cost avoid ORDER BY messageHash waku-org/nwaku
2 participants
@gabrielmer @Ivansete-status