Performance of Query 2 in TPC-H

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Please see this case:

TPC-H query 2:

select
    s_acctbal,
    s_name,
    n_name,
    p_partkey,
    p_mfgr,
    s_address,
    s_phone,
    s_comment
from
    PART,
    SUPPLIER,
    PARTSUPP,
    NATION,
    REGION
where
    p_partkey = ps_partkey
    and s_suppkey = ps_suppkey
    and p_size = 30
    and p_type like '%STEEL'
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'ASIA'
    and ps_supplycost = (
        select
            min(ps_supplycost)
        from
            PARTSUPP,
            SUPPLIER,
            NATION,
            REGION
        where
            p_partkey = ps_partkey
            and s_suppkey = ps_suppkey
            and s_nationkey = n_nationkey
            and n_regionkey = r_regionkey
            and r_name = 'ASIA'
    )
order by
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey
limit
    100;



Its query plan and execution time:

                                                                                     QUERY PLAN                                                                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=66275.04..66275.05 rows=1 width=192) (actual time=268.349..268.418 rows=100 loops=1)
   ->  Sort  (cost=66275.04..66275.05 rows=1 width=192) (actual time=268.348..268.411 rows=100 loops=1)
         Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
         Sort Method: top-N heapsort  Memory: 70kB
         ->  Hash Join  (cost=37831.01..66275.03 rows=1 width=192) (actual time=230.386..268.130 rows=485 loops=1)
               Hash Cond: ((part.p_partkey = partsupp.ps_partkey) AND ((SubPlan 1) = partsupp.ps_supplycost))
               ->  Gather  (cost=1000.00..6425.40 rows=784 width=30) (actual time=0.586..0.753 rows=826 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     ->  Parallel Seq Scan on part  (cost=0.00..5347.00 rows=327 width=30) (actual time=0.082..16.979 rows=275 loops=3)
                           Filter: (((p_type)::text ~~ '%STEEL'::text) AND (p_size = 30))
                           Rows Removed by Filter: 66391
               ->  Hash  (cost=30524.01..30524.01 rows=160000 width=172) (actual time=228.502..228.506 rows=160240 loops=1)
                     Buckets: 65536  Batches: 8  Memory Usage: 4648kB
                     ->  Hash Join  (cost=408.01..30524.01 rows=160000 width=172) (actual time=4.820..165.744 rows=160240 loops=1)
                           Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
                           ->  Seq Scan on partsupp  (cost=0.00..25516.00 rows=800000 width=14) (actual time=0.013..63.459 rows=800000 loops=1)
                           ->  Hash  (cost=383.01..383.01 rows=2000 width=166) (actual time=4.789..4.792 rows=2003 loops=1)
                                 Buckets: 2048  Batches: 1  Memory Usage: 413kB
                                 ->  Hash Join  (cost=2.51..383.01 rows=2000 width=166) (actual time=0.098..3.945 rows=2003 loops=1)
                                       Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
                                       ->  Seq Scan on supplier  (cost=0.00..323.00 rows=10000 width=144) (actual time=0.013..2.060 rows=10000 loops=1)
                                       ->  Hash  (cost=2.45..2.45 rows=5 width=30) (actual time=0.053..0.055 rows=5 loops=1)
                                             Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                             ->  Hash Join  (cost=1.07..2.45 rows=5 width=30) (actual time=0.043..0.049 rows=5 loops=1)
                                                   Hash Cond: (nation.n_regionkey = region.r_regionkey)
                                                   ->  Seq Scan on nation  (cost=0.00..1.25 rows=25 width=34) (actual time=0.008..0.010 rows=25 loops=1)
                                                   ->  Hash  (cost=1.06..1.06 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
                                                         Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                         ->  Seq Scan on region  (cost=0.00..1.06 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
                                                               Filter: (r_name = 'ASIA'::bpchar)
                                                               Rows Removed by Filter: 4
               SubPlan 1
                 ->  Aggregate  (cost=48.70..48.71 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=1311)
                       ->  Nested Loop  (cost=0.85..48.70 rows=1 width=6) (actual time=0.013..0.017 rows=1 loops=1311)
                             Join Filter: (region_1.r_regionkey = nation_1.n_regionkey)
                             Rows Removed by Join Filter: 3
                             ->  Seq Scan on region region_1  (cost=0.00..1.06 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1311)
                                   Filter: (r_name = 'ASIA'::bpchar)
                                   Rows Removed by Filter: 4
                             ->  Nested Loop  (cost=0.85..47.58 rows=4 width=10) (actual time=0.009..0.016 rows=4 loops=1311)
                                   ->  Nested Loop  (cost=0.71..46.96 rows=4 width=10) (actual time=0.008..0.012 rows=4 loops=1311)
                                         ->  Index Scan using partsupp_pkey on partsupp partsupp_1  (cost=0.42..13.75 rows=4 width=10) (actual time=0.006..0.007 rows=4 loops=1311)
                                               Index Cond: (ps_partkey = part.p_partkey)
                                         ->  Index Scan using supplier_pkey on supplier supplier_1  (cost=0.29..8.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=5244)
                                               Index Cond: (s_suppkey = partsupp_1.ps_suppkey)
                                   ->  Index Scan using nation_pkey on nation nation_1  (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=5244)
                                         Index Cond: (n_nationkey = supplier_1.s_nationkey)
 Planning Time: 2.613 ms
 Execution Time: 268.610 ms
(50 rows)


After applying this patch:

diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 5be8da9e09..0f11b1cbdf 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -449,7 +449,7 @@ paraminfo_get_equal_hashops(PlannerInfo *root, ParamPathInfo *param_info,
        *binary_mode = false;
 
        /* Add join clauses from param_info to the hash key */
-       if (param_info != NULL)
+       if (false)
        {
                List       *clauses = param_info->ppi_clauses;


The query plan and execution time:

                                                                                                      QUERY PLAN                                                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6713.81..6713.81 rows=1 width=192) (actual time=86.461..86.532 rows=100 loops=1)
   ->  Sort  (cost=6713.81..6713.81 rows=1 width=192) (actual time=86.460..86.526 rows=100 loops=1)
         Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
         Sort Method: top-N heapsort  Memory: 69kB
         ->  Nested Loop  (cost=1000.72..6713.80 rows=1 width=192) (actual time=0.855..86.150 rows=485 loops=1)
               Join Filter: (region.r_regionkey = nation.n_regionkey)
               ->  Nested Loop  (cost=1000.72..6712.72 rows=1 width=196) (actual time=0.852..85.448 rows=485 loops=1)
                     Join Filter: (nation.n_nationkey = supplier.s_nationkey)
                     Rows Removed by Join Filter: 6381
                     ->  Nested Loop  (cost=1000.72..6711.16 rows=1 width=170) (actual time=0.847..83.975 rows=485 loops=1)
                           ->  Nested Loop  (cost=1000.43..6710.86 rows=1 width=34) (actual time=0.828..83.116 rows=485 loops=1)
                                 ->  Gather  (cost=1000.00..6425.40 rows=784 width=30) (actual time=0.579..7.037 rows=826 loops=1)
                                       Workers Planned: 2
                                       Workers Launched: 2
                                       ->  Parallel Seq Scan on part  (cost=0.00..5347.00 rows=327 width=30) (actual time=0.094..17.686 rows=275 loops=3)
                                             Filter: (((p_type)::text ~~ '%STEEL'::text) AND (p_size = 30))
                                             Rows Removed by Filter: 66391
                                 ->  Memoize  (cost=0.43..133.03 rows=1 width=14) (actual time=0.076..0.092 rows=1 loops=826)
                                       Cache Key:
                                       Cache Mode: logical
                                       Hits: 0  Misses: 826  Evictions: 825  Overflows: 0  Memory Usage: 1kB
                                       ->  Index Scan using partsupp_pkey on partsupp  (cost=0.42..133.02 rows=1 width=14) (actual time=0.075..0.091 rows=1 loops=826)
                                             Index Cond: (ps_partkey = part.p_partkey)
                                             Filter: (ps_supplycost = (SubPlan 1))
                                             Rows Removed by Filter: 3
                                             SubPlan 1
                                               ->  Aggregate  (cost=30.11..30.12 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=3304)
                                                     ->  Nested Loop  (cost=0.86..30.11 rows=1 width=6) (actual time=0.013..0.017 rows=1 loops=3304)
                                                           Join Filter: (region_1.r_regionkey = nation_1.n_regionkey)
                                                           Rows Removed by Join Filter: 3
                                                           ->  Seq Scan on region region_1  (cost=0.00..1.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=3304)
                                                                 Filter: (r_name = 'ASIA'::bpchar)
                                                                 Rows Removed by Filter: 4
                                                           ->  Nested Loop  (cost=0.86..29.00 rows=4 width=10) (actual time=0.005..0.016 rows=4 loops=3304)
                                                                 ->  Nested Loop  (cost=0.72..28.37 rows=4 width=10) (actual time=0.004..0.011 rows=4 loops=3304)
                                                                       ->  Index Scan using partsupp_pkey on partsupp partsupp_1  (cost=0.42..13.75 rows=4 width=10) (actual time=0.002..0.002 rows=4 loops=3304)
                                                                             Index Cond: (ps_partkey = part.p_partkey)
                                                                       ->  Memoize  (cost=0.30..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=13216)
                                                                             Cache Key:
                                                                             Cache Mode: logical
                                                                             Hits: 0  Misses: 13216  Evictions: 13215  Overflows: 0  Memory Usage: 1kB
                                                                             ->  Index Scan using supplier_pkey on supplier supplier_1  (cost=0.29..8.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=13216)
                                                                                   Index Cond: (s_suppkey = partsupp_1.ps_suppkey)
                                                                 ->  Index Scan using nation_pkey on nation nation_1  (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=13216)
                                                                       Index Cond: (n_nationkey = supplier_1.s_nationkey)
                           ->  Index Scan using supplier_pkey on supplier  (cost=0.29..0.30 rows=1 width=144) (actual time=0.001..0.001 rows=1 loops=485)
                                 Index Cond: (s_suppkey = partsupp.ps_suppkey)
                     ->  Seq Scan on nation  (cost=0.00..1.25 rows=25 width=34) (actual time=0.000..0.001 rows=14 loops=485)
               ->  Seq Scan on region  (cost=0.00..1.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=485)
                     Filter: (r_name = 'ASIA'::bpchar)
                     Rows Removed by Filter: 2
 Planning Time: 2.669 ms
 Execution Time: 86.712 ms
(53 rows)


The estimated cost is reduced by 90%, and the execution time is reduced by 68%. The second query plan includes the operation Memoize, while the first query plan does not. I am wondering if we can optimize the logic anywhere to enable the second query plan.

Environment:
For PostgreSQL, I used the default configuration file.
For the hardware, my disk is HDD.
For the benchmark, I used 1 GB data, and my entire data folder can be downloaded here: https://drive.google.com/file/d/1ZBLHanIRwxbaMQIhRUSPv4I7y8g_0AWi/view?usp=sharing
The connection string is: postgresql://ubuntu:ubuntu@127.0.0.1:5432/tpch"

tpch=# select version();
                                             version                                              
--------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit
(1 row)



Best regards,

Jinsheng Ba

 

Notice: This email is generated from the account of an NUS alumnus. Contents, views, and opinions therein are solely those of the sender.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux