Unstable Top-N Pushdown

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: top-n下推不稳定

| username: h5n1

Update SQL, there are two execution plans, the difference is that top-n is not pushed down to TiKV.

Execution Plan 1:
Top-n is pushed down normally, actrows 3

Execution Plan 2:
Top-n is not pushed down, actrows 10

| username: tidb菜鸟一只 | Original post link

Can I add a hint? /*+ LIMIT_TO_COP() */

| username: h5n1 | Original post link

Even without hints, it can be pushed down. For example, as mentioned earlier, 687 times were not pushed down, but 42 times were pushed down.

| username: redgame | Original post link

Are the statistics accurate?

| username: h5n1 | Original post link

This doesn’t seem to work either.

image

| username: h5n1 | Original post link

Binding didn’t work either.

| username: jansu-dev | Original post link

Hello, can this be manually reproduced in the MySQL client?
If so, you can create a replayer to see if there are any new findings.
If not, it might be quite mysterious :joy:

| username: h5n1 | Original post link

It looks like everything has been pushed down.

| username: jansu-dev | Original post link

Let’s ask the experts at the conference, I currently have no ideas :joy:

| username: tiancaiamao | Original post link

Pay attention to the plan where topn is not pushed down. Besides topn not being pushed down, there is also a UnionScan operator. This operator is generally used when performing read/write operations within a transaction.

So you can verify it like this to see if the scenario does not use topn pushdown:

begin 
insert some data
update xxx where xxx order by limit 1
rollback

And then execute directly without begin:

update xxx where xxx order by limit 1

If it is this issue, you can raise an issue with us. It seems that the union scan within the transaction did not handle topn pushdown properly.

@h5n1

| username: h5n1 | Original post link

Manually tested twice, it looks like this:

@tiancaiamao

| username: tiancaiamao | Original post link

Create an issue Make it possible to push TopN down cross UnionScan · Issue #46755 · pingcap/tidb · GitHub
And we’ll handle it later.

| username: Fly-bird | Original post link

I am here to learn.

| username: 天蓝色的小九 | Original post link

Waiting for the best answer, then I’ll study it.

| username: h5n1 | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.