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.

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

Low performance for transactions endpoint with sort param #2201

Closed
lsilvs opened this Issue Jul 5, 2018 · 2 comments

Comments

Projects
3 participants
@lsilvs

lsilvs commented Jul 5, 2018

Expected behavior

I expect the /api/transactions?sort=timestamp:desc&limit=20 request to not take too long.

Actual behavior

Can take more than 10 seconds depending of the db size

Steps to reproduce

Request /api/transactions?sort=timestamp:desc&limit=20

Which version(s) does this affect? (Environment, OS, etc...)

1.0

@lsilvs

This comment has been minimized.

Show comment
Hide comment
@lsilvs

lsilvs Jul 5, 2018

After a quick analise I've noticed that the low permormance was introduced in this PR #1971

Adding a second column to the ORDER BY statement makes the query very slow.
Using ORDER BY timestamp alone or ORDER BY rowId alone is fine but the combination of both is very expensive.

lsilvs commented Jul 5, 2018

After a quick analise I've noticed that the low permormance was introduced in this PR #1971

Adding a second column to the ORDER BY statement makes the query very slow.
Using ORDER BY timestamp alone or ORDER BY rowId alone is fine but the combination of both is very expensive.

@MaciejBaj MaciejBaj added this to New Issues in Version 1.2.0 via automation Aug 2, 2018

@MaciejBaj MaciejBaj added this to the Version 1.2.0 milestone Aug 2, 2018

@4miners

This comment has been minimized.

Show comment
Hide comment
@4miners

4miners Aug 14, 2018

Member

We need to add 2 indexes for each field we're using for soring:

  • timestamp
  • amount
  • fee
  • type

It's because multicolumn index is not used when sorting DESC, for example:

CREATE INDEX trs_timestamp_row_id_asc ON trs (timestamp, "rowId" ASC);

t_timestamp ASC, "t_rowId" ASC LIMIT 100 OFFSET 0 - 3.998 ms
t_timestamp DESC, "t_rowId" ASC LIMIT 100 OFFSET 0 - 19058.764 ms

When we add both indexes:

CREATE INDEX trs_timestamp_asc_row_id_asc ON trs (timestamp ASC, "rowId" ASC);
CREATE INDEX trs_timestamp_desc_row_id_asc ON trs (timestamp DESC, "rowId" ASC);

Then actual queries utilize them:
t_timestamp ASC, "t_rowId" ASC LIMIT 100 OFFSET 0 - 1.358 ms
t_timestamp DESC, "t_rowId" ASC LIMIT 100 OFFSET 0 - 1.706 ms

Also we can remove indexes on timestamp and type, as new indexes covers them and performance is the same.

Storage use for added indexes:
timestamp - 2 x 41 MB
amount - 2 x 58 MB
fee - 2 x 58 MB
type - 2 x 41 MB

Member

4miners commented Aug 14, 2018

We need to add 2 indexes for each field we're using for soring:

  • timestamp
  • amount
  • fee
  • type

It's because multicolumn index is not used when sorting DESC, for example:

CREATE INDEX trs_timestamp_row_id_asc ON trs (timestamp, "rowId" ASC);

t_timestamp ASC, "t_rowId" ASC LIMIT 100 OFFSET 0 - 3.998 ms
t_timestamp DESC, "t_rowId" ASC LIMIT 100 OFFSET 0 - 19058.764 ms

When we add both indexes:

CREATE INDEX trs_timestamp_asc_row_id_asc ON trs (timestamp ASC, "rowId" ASC);
CREATE INDEX trs_timestamp_desc_row_id_asc ON trs (timestamp DESC, "rowId" ASC);

Then actual queries utilize them:
t_timestamp ASC, "t_rowId" ASC LIMIT 100 OFFSET 0 - 1.358 ms
t_timestamp DESC, "t_rowId" ASC LIMIT 100 OFFSET 0 - 1.706 ms

Also we can remove indexes on timestamp and type, as new indexes covers them and performance is the same.

Storage use for added indexes:
timestamp - 2 x 41 MB
amount - 2 x 58 MB
fee - 2 x 58 MB
type - 2 x 41 MB

@MaciejBaj MaciejBaj closed this in 2ae81c7 Aug 16, 2018

Version 1.2.0 automation moved this from New Issues to Closed Issues Aug 16, 2018

@MaciejBaj MaciejBaj added this to Open Issues in Version 1.0.0 via automation Aug 25, 2018

@MaciejBaj MaciejBaj removed this from Closed Issues in Version 1.2.0 Aug 25, 2018

@MaciejBaj MaciejBaj removed this from the Version 1.2.0 milestone Aug 25, 2018

@diego-G diego-G moved this from Open Issues to Closed Issues in Version 1.0.0 Aug 29, 2018

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