Question 89835 in PostgreSQL Question posted in
The official documentation can be found here.

postgres optimizing "is null" condition on left join – Postgresql

I run the below ‘is null’ condition query on the postgres – 11.9 version and it takes almost 1500-2000 ms for each run. I changed the default statistics to 1000 for the database and executed reindex,vacuum and analyze on the tables involved in the query. Still the query takes ~1100-1300 ms.

If I change the condition to ‘is not null’ and it executes within 150 ms. And If I disable the merge join in the instance and run the same query with ‘is null’ clause, it executes within 50ms and the execution plan changes the join from merge to hash join. I rewrote the query to avoid the ‘is null’ key , but still it generates the same execution plan of 1100 ms.

How can I enforce the hash join in the plan without disabling the merge join? Any suggestions to make it better?

Actual Query:

select ev.id from logevent ev
left join flowtoken FT on ev.uri = 'xxx://xxx/WorkflowToken?id=''' || FT.id || ''' &xx=''Token'''
where 
ev.uri like 'xxx://xxx/WorkflowToken?id=%'
and FT.id is null

Table definition:

table – flowtoken

column – id character varying(100) not null, primary key indexes btree(id)

table – logevent

column – id character varying(100) not null, primary key indexes btree(id)
uri character varying(1024) not null, "idx_logeventuri" btree(uri)

Actual Execution Plan:

 Gather (cost=23655.83..33636.20 rows=72532 width=33) (actual time=783.987..1117.367 rows=703 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Merger Anti Join    (cost-22644.83..25383.00 rows=30222 width=33) (actual time=706.542...972.300 rows=234 loops=3)
          Merge Cond: ((ev.uri)::text = ((('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text)))
          ->    Sort (cost=17003.08..17154.19 rows=60443 width=140) (actual time=626.520..739.990 rows=48237 loops=3)
                    sort key:ev.uri
                    sort method: external merge disk: 8136kb
                    worker 0:   sort method: external merge disk: 6184kb
                    worker 1:   sort method: external merge disk: 6184kb
                    ->  Parallel Seq Scan on logevent ev (cost=0.00..7862.91 rows=60443 width=140) (actual time=0.022..19.463 rows=48237 loops=3)
                            Filter: ((uri)::text ~~ 'xxx://xxx/WorkflowToken?id=%'::text)
                            Rows Removed by filter: 142
          ->    Sort (cost=5641.08..5757.19 rows=46369 width=37) (actual time=77.520..164.990 rows=46368 loops=3)
                    sort key:((('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text)))
                    sort method: external merge disk: 6992kb
                    worker 0:   sort method: external merge disk: 6992kb
                    worker 1:   sort method: external merge disk: 6992kb
                    ->  Index Only Scan using pk_flowtoken on flowtoken  FT (cost=0.41..2047.91 rows=46369 width=37) (actual time=0.049..7.539 rows=46369 loops=3)
                        
                                Heap Fetches: 0

 Planning Time: 1.381 ms
 Execution Time: 1120.433 ms

Execution Plan for ‘is not null’ condition:

Hash Join   (cost=13709.89..39695.69 rows=400812 width=33) (actual time=86.005..149.488 rows=144007 loops=1)
    Hash Cond: (('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text) = (ev.uri)::text)
    ->  Index Only Scan using pk_flowtoken on flowtoken FT (cost=0.41..2163.87 rows=46369 width=37) (actual time=0.020..4.256 rows=46369 loops=1)
        Index Cond: (id is not null)
        Heap fetches: 0
    ->  Hash (cost=8921.19..8921.19 rows=145063 width=140) (actual time=85.828..85..829 rows=144710 loops=1)    
            Buckets: 32768 Batches:8 Memory Usage: 3228kb
            ->  seq scan on logevent ev (cost=0.00..8921.19 rows=145063 width=140) (actual time=0.013..46.118 rows=144710 loops=1)
                    Filter:((uri)::text ~~ 'xxx://xxx/WorkflowToken?id=%'::text)
                    Rows Removed by filter: 425

 Planning Time: 0.417 ms
 Execution Time: 153.211 ms

** Executution Plan after disabled merge join**

 Gather (cost=3018.96..85692.57 rows=72532 width=33) (actual time=15.420..50.290 rows=703 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Parallel Hash Anti Join (cost=2018.96..77439.37 rows=30222 width=33) (actual time=8.375..40.668 rows=234 loops=3)
            Hash Cond: ((ev.uri)::text = ((('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text)))
            
            -> Parallel seq scan on logevent ev (cost=0.00..7862.91 rows=60443 width=140) (actual time=0.012..20.100 rows=48237 loops=3)
                Filter: ((uri)::text ~~ 'xxx://xxx/WorkflowToken?id=%'::text)
                Rows Removed by filter:142
            ->  Parallel Index Only Scan using pk_flowtoken on flowtoken FT (cost=0.41..1777.46 rows=19320 width=37) (actual time=0.031..1.829 rows=15456 loops=3)
                    Heap Fetches: 0

Planning Time: 0.240 ms
Execution Time: 50.363 ms

Query Rewrote:

select ev.id 
from logevent ev 
where not exists ( select * 
                   from flowtoken FT 
                   where ev.uri = 'xxx://xxx/WorkflowToken?id=''' || FT.id || ''' &xx=''Token''') 
 and ev.uri like 'xxx://xxx/WorkflowToken?id=%'

Execution plan after increasing the work mem to 64mb

Gather  (cost=19304.83..29296.20 rows=72532 width=33) (actual time=1029.283..1114.902 rows=703 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Merger Anti Join    (cost-18304.83..21043.00 rows=30222 width=33) (actual time=821.942...901.624 rows=234 loops=3)
          Merge Cond: ((ev.uri)::text = ((('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text)))
          ->    Sort (cost=12663.08..12814.19 rows=60443 width=140) (actual time=746.900..753.702 rows=48237 loops=3)
                    sort key:ev.uri
                    sort method: quicksort memory: 17703kb
                    worker 0:   sort method: quicksort memory: 12731kb
                    worker 1:   sort method: quicksort memory: 12614kb
                    ->  Parallel Seq Scan on logevent ev (cost=0.00..7862.91 rows=60443 width=140) (actual time=0.011..23.60 rows=48237 loops=3)
                            Filter: ((uri)::text ~~ 'xxx://xxx/WorkflowToken?id=%'::text)
                            Rows Removed by filter: 142
          ->    Sort (cost=5641.08..5757.19 rows=46369 width=37) (actual time=74.520..77.532 rows=46368 loops=3)
                    sort key:((('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text)))
                    sort method: quicksort memory:13853kb
                    worker 0:   sort method: quicksort memory: 13853kb
                    worker 1:   sort method: quicksort memory: 13853kb
                    ->  Index Only Scan using pk_flowtoken on flowtoken  FT (cost=0.41..2047.91 rows=46369 width=37) (actual time=0.049..7.539 rows=46369 loops=3)
                        
                                Heap Fetches: 0

 Planning Time: 0.445 ms
 Execution Time: 1118.389 ms
'''
Tags:

2

Answers


  1. 0

    Stick with the rewritten query with the NOT EXISTS clause. Extended statistics on the expression (available since PostgreSQL v14) may improve the estimate and get you a better plan:

    CREATE STATISTICS url_stats
ON ('xxx://xxx/WorkflowToken?id=''' || id || ''' &xx=''Token''')
FROM flowtoken;

ANALYZE flowtoken;
    Login or Signup to reply.
  2. 0

    To my surprise, I can reproduce the horrible sort performance just by using en_US.UTF-8. Maybe that is because all of the data has the same prefix which must be tested equal before it gets to the part that matters. (I don’t think en_US.UTF-8 is normally all that much slower than C).

    Hash antijoins are severely (and in my opinion, unwisely) punished by the planner when it can’t estimate the number of distinct values on the 2nd side, which it generally can’t when the join is being done on an expression rather than a simple column. Laurenz’s answer provides one way to address that but it only works on newer versions.

    But there is another way on older versions, create an expression index.

     create index on flowtoken (('xxx://xxx/WorkflowToken?id=''' || id || ''' &xx=''Token'''));
 analyze flowtoken;

    That index will probably not be used in your query, but it will trigger the collection of statistics and those statistics will get used in planning the query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search