Hello,

We have 2 TPC-H queries which fetch the same tuples but have significant query 
execution time differences (4.3 times).


We are sharing a pair of TPC-H queries that exhibit this performance difference:


First query:

SELECT "ps_comment",

       "ps_suppkey",

       "ps_supplycost",

       "ps_partkey",

       "ps_availqty"

FROM   "partsupp"

WHERE  "ps_partkey" + 16 < 1

        OR "ps_partkey" = 2

GROUP  BY "ps_partkey",

          "ps_suppkey",

          "ps_availqty",

          "ps_supplycost",

          "ps_comment"


Second query:

SELECT "ps_comment",

       "ps_suppkey",

       "ps_supplycost",

       "ps_partkey",

       "ps_availqty"

FROM   "partsupp"

WHERE  "ps_partkey" + 16 < 1

        OR "ps_partkey" = 2

GROUP  BY "ps_comment",

          "ps_suppkey",

          "ps_supplycost",

          "ps_partkey",

          "ps_availqty"


* Actual Behavior

We executed both queries on the TPC-H benchmark of scale factor 5: the first 
query takes over 1.7 seconds, while the second query only takes 0.4 seconds.
We think the time difference results from different plans selected. 
Specifically, in the first (slow) query, the DBMS performs an index scan on 
table partsupp using the covering index (ps_partkey, ps_suppkey), while the 
second (fast) query performs a parallel scan on (ps_suppkey, ps_partkey).


*  Query Execution Plan

  *   First query:

                                                       QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------

 Group  (cost=0.43..342188.58 rows=399262 width=144) (actual 
time=0.058..1737.659 rows=4 loops=1)

   Group Key: ps_partkey, ps_suppkey

   Buffers: shared hit=123005 read=98055

   ->  Index Scan using partsupp_pkey on partsupp  (cost=0.43..335522.75 
rows=1333167 width=144) (actual time=0.055..1737.651 rows=4 loops=1)

         Filter: (((ps_partkey + 16) < 1) OR (ps_partkey = 2))

         Rows Removed by Filter: 3999996

         Buffers: shared hit=123005 read=98055

 Planning Time: 0.926 ms

 Execution Time: 1737.754 ms

(9 rows)





  *   Second query:

                                                                                
                                                          QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------

 Group  (cost=250110.68..350438.93 rows=399262 width=144) (actual 
time=400.353..400.361 rows=4 loops=1)

   Group Key: ps_suppkey, ps_partkey

   Buffers: shared hit=5481 read=24093

   ->  Gather Merge  (cost=250110.68..346446.31 rows=798524 width=144) (actual 
time=400.351..406.741 rows=4 loops=1)

         Workers Planned: 2

         Workers Launched: 2

         Buffers: shared hit=15151 read=72144

         ->  Group  (cost=249110.66..253276.80 rows=399262 width=144) (actual 
time=395.882..395.883 rows=1 loops=3)

               Group Key: ps_suppkey, ps_partkey

               Buffers: shared hit=15151 read=72144

               ->  Sort  (cost=249110.66..250499.37 rows=555486 width=144) 
(actual time=395.880..395.881 rows=1 loops=3)

                     Sort Key: ps_suppkey, ps_partkey

                     Sort Method: quicksort  Memory: 25kB

                     Worker 0:  Sort Method: quicksort  Memory: 25kB

                     Worker 1:  Sort Method: quicksort  Memory: 25kB

                     Buffers: shared hit=15151 read=72144

                     ->  Parallel Seq Scan on partsupp  (cost=0.00..116363.88 
rows=555486 width=144) (actual time=395.518..395.615 rows=1 loops=3)

                           Filter: (((ps_partkey + 16) < 1) OR (ps_partkey = 2))

                           Rows Removed by Filter: 1333332

                           Buffers: shared hit=15065 read=72136

 Planning Time: 0.360 ms

 Execution Time: 406.880 ms

(22 rows)







*Expected Behavior

Since these two queries are semantically equivalent, we were hoping that 
PostgreSQL would evaluate them in roughly the same amount of time.
It looks to me that different order of group by clauses triggers different 
plans: when the group by clauses (ps_partkey, ps_suppkey) is the same as the 
covering index, it will trigger an index scan on associated columns;
however,  when the group by clauses have different order than the covering 
index (ps_suppkey, ps_partkey), the index scan will not be triggered.
Given that the user might not pay close attention to this subtle difference, I 
was wondering if it is worth making these two queries have the same and 
predictable performance on Postgresql.


*Test Environment

Ubuntu 20.04 machine "Linux panda 5.4.0-40-generic #44-Ubuntu SMP Tue Jun 23 
00:01:04 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux"

PostgreSQL v12.3

Database: TPC-H benchmark (with scale factor 5)

The description of table partsupp is as follows:

tpch5=# \d partsupp;

                         Table "public.partsupp"

    Column     |          Type          | Collation | Nullable | Default

---------------+------------------------+-----------+----------+---------

 ps_partkey    | integer                |           | not null |

 ps_suppkey    | integer                |           | not null |

 ps_availqty   | integer                |           | not null |

 ps_supplycost | numeric(15,2)          |           | not null |

 ps_comment    | character varying(199) |           | not null |

Indexes:

    "partsupp_pkey" PRIMARY KEY, btree (ps_partkey, ps_suppkey)

Foreign-key constraints:

    "partsupp_fk1" FOREIGN KEY (ps_suppkey) REFERENCES supplier(s_suppkey)

    "partsupp_fk2" FOREIGN KEY (ps_partkey) REFERENCES part(p_partkey)

Referenced by:

    TABLE "lineitem" CONSTRAINT "lineitem_fk2" FOREIGN KEY (l_partkey, 
l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey)






*Here are the steps for reproducing our observations:

  1.  Download the dataset from the link: 
https://drive.google.com/file/d/13rFa1BNDi4e2RmXBn-yEQkcqt6lsBu1c/view?usp=sharing

  2.  Set up TPC-H benchmark

tar xzvf tpch5_postgresql.tar.gz

cd tpch5_postgresql

db=tpch5

createdb $db

psql -d $db < dss.ddl

for i in `ls *.tbl`

do

    echo $i

    name=`echo $i|cut -d'.' -f1`

    psql -d $db -c "COPY $name FROM '`pwd`/$i' DELIMITER '|' ENCODING 'LATIN1';"

done

psql -d $db < dss_postgres.ri

  1.  Execute the queries

Reply via email to