Two SQL queries are fast individually, but extremely slow when using UNION ALL

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

Original topic: 两个查询SQL语句,单独查询很快,但是两个查询union all超级慢

| username: seiang

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.1
[Reproduction Path]

SQL Statement 1:

SELECT 
    dim_adid,
    dim_creativeid,
    dim_materialid,
    '999' AS dim_djid,
    '999' AS dim_videoid,
    dim_advsiteid,
    dim_timeslice
FROM
    tab1
WHERE
    timeslice >= 20240301
        AND timeslice <= 20240531;

Execution plan is as follows:

SQL Statement 2:

SELECT 
    999 AS dim_adid,
    999 AS dim_creativeid,
    999 AS dim_materialid,
    dim_djid,
    dim_videoid,
    dim_advsiteid,
    dim_timeslice
FROM
    tab2
WHERE
    timeslice >= 20240301
        AND timeslice <= 20240531;

Execution plan is as follows:

The above two SQL statements execute very quickly on their own, around 0.1s, but when the two SQL statements are queried through UNION ALL, it is particularly slow, taking more than 50s.

The execution plan for UNION ALL is as follows:

[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: 濱崎悟空 | Original post link

First, try adding an index to tab2 to avoid a full table scan.

| username: WalterWj | Original post link

Does this look like a full table scan? Is it fast? You should have the client add a layer of LIMIT 100;.

| username: seiang | Original post link

Indeed, the client added a limit restriction. Without the limit restriction, it’s not fast either. The field timeslice is of bigint type. Is there an optimization solution in this case?

| username: WalterWj | Original post link

Uh, is it simply missing an index?

| username: seiang | Original post link

There is a composite primary key
PRIMARY KEY (dim_timeslice, dim_blid, dim_plid, dim_apptype, dim_appid, dim_dwsiteid, dim_advsiteid, dim_djid, dim_videoid)

| username: WalterWj | Original post link

It looks like timeslice is not included in it?

| username: seiang | Original post link

Inside, it is this dim_timeslice field.

| username: WalterWj | Original post link

Isn’t this your condition…

| username: seiang | Original post link

Sorry, there was a bit of an issue with the post.

| username: zhaokede | Original post link

Has adding an index to tab2’s timeslice improved performance?

| username: seiang | Original post link

Tab2 has a composite primary key index just like tab1, but the index is not being used.

| username: Kongdom | Original post link

:flushed: Added a limit but it doesn’t show in the execution plan?

| username: zhh_912 | Original post link

Explain where the execution plan is slow.

| username: TIDB-Learner | Original post link

My learning experience is to understand the execution plan.

| username: 鱼跃龙门 | Original post link

tb2 did not use the index, is there a problem with the index creation?

| username: TiDBer_HUfcQIJx | Original post link

Lack of indexes.

| username: lemonade010 | Original post link

Try using EXPLAIN ANALYZE to see where the actual time is being spent.

| username: forever | Original post link

What is the proportion of data returned relative to the entire table?

| username: FutureDB | Original post link

What is the composite primary key index of tab2? Can you post it for us to see?