[PHOENIX-2943] Performance of parallel order by query is > 30X slower than serial execution

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Mujtaba Chohan
• Created: 26/May/16 19:15
• Updated: 16/Jan/19 01:07

Description

select /*+SERIAL*/  L_DISCOUNT, L_QUANTITY from lineitem_encoded order by (l_discount,L_QUANTITY) limit 1;
+-------------+-------------+
| L_DISCOUNT  | L_QUANTITY  |
+-------------+-------------+
| 0.04        | 17          |
+-------------+-------------+
1 row selected (0.129 seconds)

select L_DISCOUNT, L_QUANTITY from lineitem_encoded order by (l_discount,L_QUANTITY) limit 1;
+-------------+-------------+
| L_DISCOUNT  | L_QUANTITY  |
+-------------+-------------+
| 0.04        | 17          |
+-------------+-------------+
1 row selected (4.63 seconds)

explain select /*+SERIAL*/  L_DISCOUNT, L_QUANTITY from lineitem_encoded order by (l_discount,L_QUANTITY) limit 1;
+------------------------------------------------------------------------------------------------+
|                                              PLAN                                              |
+------------------------------------------------------------------------------------------------+
| CLIENT 417-CHUNK 5978838 ROWS 4150009682 BYTES SERIAL 417-WAY FULL SCAN OVER LINEITEM_ENCODED  |
|     SERVER TOP 1 ROW SORTED BY [(L_DISCOUNT, L_QUANTITY)]                                      |
| CLIENT MERGE SORT                                                                              |
+------------------------------------------------------------------------------------------------+
3 rows selected (0.016 seconds)
0: jdbc:phoenix:localhost> explain select  L_DISCOUNT, L_QUANTITY from lineitem_encoded order by (l_discount,L_QUANTITY) limit 1;
+--------------------------------------------------------------------------------------------------+
|                                               PLAN                                               |
+--------------------------------------------------------------------------------------------------+
| CLIENT 417-CHUNK 5978838 ROWS 4150009682 BYTES PARALLEL 417-WAY FULL SCAN OVER LINEITEM_ENCODED  |
|     SERVER TOP 1 ROW SORTED BY [(L_DISCOUNT, L_QUANTITY)]                                        |
| CLIENT MERGE SORT                                                                                |
+--------------------------------------------------------------------------------------------------+
3 rows selected (0.015 seconds)

Profiler information on this to be added soon.