pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16886: string equivalence predicate causes system slowdown
2+ messages / 2 participants
[nested] [flat]

* BUG #16886: string equivalence predicate causes system slowdown
@ 2021-02-22 20:54  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2021-02-22 20:54 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: liuxy@gatech.edu

The following bug has been logged on the website:

Bug reference:      16886
Logged by:          XINYU LIU
Email address:      liuxy@gatech.edu
PostgreSQL version: 12.3
Operating system:   Ubuntu 20.04
Description:        

Hello,

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

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

First query:
SELECT          nation.n_name AS n_name 
FROM            partsupp 
LEFT OUTER JOIN nation 
ON   true 
WHERE           nation.n_name IS NOT distinct FROM 'ALGERIA';  

Second query:
SELECT nation.n_name AS n_name 
FROM   partsupp 
       LEFT OUTER JOIN nation 
                    ON true 
WHERE  nation.n_name = 'ALGERIA'; 

Actual Behavior
We executed both queries on the TPC-H benchmark of scale factor 5: the first
query takes over 23 seconds, while the second query only takes 1.4 seconds.
We think the time difference results from different plans selected.
Specifically, in the first (slow) query, the optimizer does not push the
predicate through the LEFT JOIN operator.

Query Execution Plan
First query:
                                                      QUERY PLAN            
                                            
---------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..1627144.18 rows=3999849 width=104)
(actual time=0.102..23684.787 rows=4000000 loops=1)
   Filter: (NOT (nation.n_name IS DISTINCT FROM 'ALGERIA'::bpchar))
   Rows Removed by Filter: 96000000
   ->  Seq Scan on partsupp  (cost=0.00..127199.49 rows=3999849 width=0)
(actual time=0.077..883.386 rows=4000000 loops=1)
   ->  Materialize  (cost=0.00..1.38 rows=25 width=104) (actual
time=0.000..0.002 rows=25 loops=4000000)
         ->  Seq Scan on nation  (cost=0.00..1.25 rows=25 width=104) (actual
time=0.011..0.019 rows=25 loops=1)
 Planning Time: 0.159 ms
 Execution Time: 23862.155 ms






Second query:
                                                                      QUERY
PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..167199.29 rows=3999849 width=104) (actual
time=0.088..1273.682 rows=4000000 loops=1)
   ->  Seq Scan on nation  (cost=0.00..1.31 rows=1 width=104) (actual
time=0.021..0.031 rows=1 loops=1)
         Filter: (n_name = 'ALGERIA'::bpchar)
         Rows Removed by Filter: 24
   ->  Seq Scan on partsupp  (cost=0.00..127199.49 rows=3999849 width=0)
(actual time=0.064..830.281 rows=4000000 loops=1)
 Planning Time: 0.139 ms
 Execution Time: 1435.122 ms
(7 rows)





Expected Behavior
Since these two queries are semantically equivalent, we were hoping that
PostgreSQL will evaluate them in roughly the same amount of time. 

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 nation is as follows:
tpch5=# \d+ nation;
                                             Table "public.nation"
   Column    |          Type          | Collation | Nullable | Default |
Storage  | Stats target | Description 
-------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 n_nationkey | integer                |           | not null |         |
plain    |              | 
 n_name      | character(25)          |           | not null |         |
extended |              | 
 n_regionkey | integer                |           | not null |         |
plain    |              | 
 n_comment   | character varying(152) |           |          |         |
extended |              | 
Indexes:
    "nation_pkey" PRIMARY KEY, btree (n_nationkey)
Foreign-key constraints:
    "nation_fk1" FOREIGN KEY (n_regionkey) REFERENCES region(r_regionkey)
Referenced by:
    TABLE "customer" CONSTRAINT "customer_fk1" FOREIGN KEY (c_nationkey)
REFERENCES nation(n_nationkey)
    TABLE "supplier" CONSTRAINT "supplier_fk1" FOREIGN KEY (s_nationkey)
REFERENCES nation(n_nationkey)




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
3. Execute the queries



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #16886: string equivalence predicate causes system slowdown
@ 2021-03-01 11:25  Richard Guo <guofenglinux@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Guo @ 2021-03-01 11:25 UTC (permalink / raw)
  To: liuxy@gatech.edu; pgsql-bugs@lists.postgresql.org

Hi,

On Tue, Feb 23, 2021 at 5:26 AM PG Bug reporting form <
noreply@postgresql.org> wrote:

>
> Actual Behavior
> We executed both queries on the TPC-H benchmark of scale factor 5: the
> first
> query takes over 23 seconds, while the second query only takes 1.4 seconds.
> We think the time difference results from different plans selected.
> Specifically, in the first (slow) query, the optimizer does not push the
> predicate through the LEFT JOIN operator.
>

Well, in the first (slow) query, the qual "nation.n_name IS NOT distinct
FROM 'ALGERIA'" is not considered as strict. So it cannot be used to
reduce the left outer join here. Meanwhile, since it is a qual above the
outer join and references nullable Var 'nation.n_name', it cannot be
pushed down below the outer join, otherwise we would have unexpected
null-extended rows from the outer join.

Thanks
Richard

^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2021-03-01 11:25 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-22 20:54 BUG #16886: string equivalence predicate causes system slowdown PG Bug reporting form <noreply@postgresql.org>
2021-03-01 11:25 ` Richard Guo <guofenglinux@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox