perf: simple select queries are inefficient compared to PG

[lmatz]

database0-cur.log

Researcher @suyZhong pointed out today that when running all the queries in the file above, PG takes less than 1 second while RW runs for a very long time.

Reproduced it on my laptop.

PG:

martin@Martins-MacBook-Pro risingwave % time psql -f database0-cur.log
...
psql -f database0-cur.log  0.13s user 0.07s system 40% cpu 0.510 total

RW:

time psql -h localhost -p 4566 -d dev -U root -f database0-cur.log
...
psql -h localhost -p 4566 -d dev -U root -f database0-cur.log  0.19s user 0.19s system 0% cpu 1:45.13 total

Although some queries are slow due to "feature unimplemented" and flushing insert statements, they take only a very small fraction of all the queries.

We remark that query_mode has been set to local in the file above.

[BugenZhao]

We may leverage the distributed tracing to identify the bottleneck of one batch query.

[github-actions]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean.
Don't worry if you think the issue is still valuable to continue in the future.
It's searchable and can be reopened when it's time. 😄