A few weeks ago I was let go from a project and I received a message on Katalk from the project leader 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 earning income. 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 luxurious 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.
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, the 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 with the project. I need to have lunch with my team members. HANSOME GUY : Then how about tomorrow dinner? HANSOME GUY : Hello? HANSOME GUY : Are you bysy now? (CUTE GIRL 님이 나갔습니다.)
Since then, I have lost touch with the project leader.
FOOTNOTE When you use an alias (temporary name) in the SELECT list on the ORDER BY clause, the optimizer will attempt to replace the alias with the original name 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.
Nice to talk with you again. Will you help me with a query, please?
You kicked me out of the project. My wife is negging at me because I am not earning income.
Without you, the Korean database industry will lose its competitiveness in the world.
