설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 126건, 최근 0 건
 

Failure to access an index in a pagination query

글쓴이 : 모델광 날짜 : 2023-01-22 (일) 23:21 조회 : 62
A few weeks ago I was dismissed from a project and one day I got a katalk message from a project leader who I had worked with.
Here are the messages we exchanged with some cosmetic chagnes:
CUTE GIRL : Hi?
CUTE GIRL : What's up?
CUTE GIRL : I miss you. I want to see you.
CUTE GIRL : I am in a hurry. Please answer me.
CUTE GIRL : Please.....
CUTE GIRL : Are you alive or dead?
HANSOME GUY : Alive.
CUTE GIRL : Oh! Nice to talk with you again. Will you help me with a query, please?
HANSOME GUY : Why should I help you?  You kicked me out of the project. My wife is negging at me because I am not making money.
CUTE GIRL : You are getting me wrong. It is not me but the project manager who fired you. If you help me, I can treat you to dinner at a fancy restaurant.
HANSOME GUY : Really?
CUTE GIRL : Yes. I am serious. You know I have a company credit card.
HANSOME GUY : What is the problem with you?
CUTE GIRL : I have this query and it is slow. I want to make this query go faster.

SELECT ORD_NO, TO_CHAR(ORD_DATE,'YYYYMMDD') AS ORD_DATE, COMMENT
   FROM ONLINE_ORDER
 ORDER BY ORD_DATE
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY;

HANSOME GUY : Does the table ONLINE_ORDER have an index on the column ORD_DATE ?
CUTE GIRL : How can I check if the table has an index on the column?
HANSOME GUY : Run the following query.

SELECT *
  FROM PG_INDEXES
 WHERE TABLENAME = 'online_order';

CUTE GIRL : Yes. It does have an index on the column.
HANSOME GUY : Can you check whether the query accesses the index?
CUTE GIRL : How can I check whether the query accesses the index?
HANSOME GUY : Please read the manual I have written up for the project.
CUTE GIRL : Oh, it seems that the query is not accessing the index. Here is the execution plan.

Limit  (cost=74491.89..74494.22 rows=20 width=137)
  ->  Gather Merge  (cost=74490.72..268948.67 rows=1666666 width=137)
        Workers Planned: 2
        ->  Sort  (cost=73490.70..75574.03 rows=833333 width=137)
              Sort Key: (to_char(ord_date, 'YYYYMMDD'::text))
              ->  Parallel Seq Scan on online_order  (cost=0.00..48878.67 rows=833333 width=137)

​HANSOME GUY : Hmm... The alias "ORD_DATE" in the SELECT list is the culprit of not accessing the index. Will you run the following query?

SELECT ORD_NO, TO_CHAR(ORD_DATE,'YYYYMMDD') AS ORD_DATE2, COMMENT
   FROM ONLINE_ORDER
 ORDER BY ORD_DATE
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY;

CUTE GIRL : Wow! Now it is balzingly fast. But we cannot change "ORD_DATE" to "ORD_DATE2" in the application program. We have to show the word "ORD_DATE" on the screen.
HANSOME GUY : Well, regarding dinner at a fancy restaurant, how much budget do you have?
CUTE GIRL : I can spend 10,000 won per person.
HANSOME GUY : Can you raise it to 20,000 won?
CUTE GIRL :Well...
HANSOME GUY : Well... I can not help you with the query any more.
CUTE GIRL : Wait! I will raise it to 20,000. Please show me how to make the query go faster without changing the alias "ORD_DATE"
HANSOME GUY : You can execute the query below and then you will be able to see the word "ORD_DATE" on the screeen.

SELECT ORD_NO, TO_CHAR(ORD_DATE,'YYYYMMDD') AS ORD_DATE, COMMENT
  FROM (      SELECT ORD_NO, ORD_DATE, COMMENT
                   FROM ONLINE_ORDER
                 ORDER BY ORD_DATE
                OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY
               ) AS FOO;

CUTE GIRL :Wow! Thank you very much. You are a cut above other tuners! Without you Korean database industry will lose its competitiveness in the world.
HANSOME GUY : You can say that again. By the way I am free tonight. How about having dinner today?
CUTE GIRL : I'afraid my child has a stomach ache so I have to go home early today.
HANSOME GUY : I see. How about tomorrow lunch?
CUTE GIRL : You know I am very busy on the project. I have to have lunch with my teammates.
HANSOME GUY : Then how about tomorrow dinner?
HANSOME GUY : Hello?
HANSOME GUY : Are you bysy now?
(CUTE GIRL 님이 나갔습니다.)

From that time on I have lost contact with the project leader.

FOOTNOTE
When you put the alias (temporary name) in the select list on the ORDER BY clause, the optimizer tries to change the alias into the original in the SELECT list.
When you run the first query in the katalk message, PostgreSQL transforms the query into the following:

SELECT ORD_NO, TO_CHAR(ORD_DATE,'YYYYMMDD') AS ORD_DATE, COMMENT
   FROM ONLINE_ORDER
 ORDER BY TO_CHAR(ORD_DATE,'YYYYMMDD')
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY;

​Since there is no index on TO_CHAR(ORD_DATE,'YYYYMMDD'), PostgreSQL failed to access an index on the column ORD_DATE.

 

postgresdba.com