Thread

1. partition table optimizer join cost misestimation

   James Pang <jamespang886@gmail.com> — 2025-04-02T10:18:57Z

   Hi,
      Postgresq v14.8, we found optimizer doest not take "merge append" cost
   into sql plan total cost and then make a bad sql plan. attached please find
   details.
   
     Query: masking table and column names :
   
      select ....
      from tablea aa
           inner join tableb bb on aa.ind1 = bb.ind1
   inner join tablec cc  on aa.ind2 = cc.ind2
      where aa.ind3 in ($1)
      order by cc.id3;
   
   optimizer, it choose merge join with full index scan with a lot of rows and
   huge IO needed,
   looks like optimizer does not take into account the "Merge Append" huge
   cost into the whole SQL plan cost,
   so it thought only Sort and Merge join cost and make it to choose mergejoin
   instead of nestloop.
   
   --bad plan, it took hundreds of seconds to complete.
   Sort  (cost=9624.33..9677.60 rows=21306 width=981)
      Sort Key: cc.id3
      ->  Nested Loop  (cost=99.78..2717.44 rows=21306 width=981)
            ->  Merge Join  (cost=99.35..2009.19 rows=21306 width=915)
                  Merge Cond: (cc.ind2 = aa.id2)
                  ->  Merge Append  (cost=8.15..17046177.32 rows=98194074
   width=903)            <<< merge append huge cost but looks like optimizer
   does not take this into total plan cost yet.
   
   --good plan, off mergejoin to make it chose nestloop and it took only 20
   milliseconds to complete.
   
    Sort  (cost=19618.71..19671.98 rows=21306 width=981)
      Sort Key: cc.ind2
      ->  Nested Loop  (cost=0.72..12711.82 rows=21306 width=981)
            ->  Nested Loop  (cost=0.29..12003.57 rows=21306 width=915)
                  ->  Append  (cost=0.29..42.46 rows=30 width=28)
   
   Thanks,
   
   James
   

2. Re: partition table optimizer join cost misestimation

   Andrei Lepikhov <lepihov@gmail.com> — 2025-04-02T11:03:37Z

   On 4/2/25 12:18, James Pang wrote:
   > Hi,
   >     Postgresq v14.8, we found optimizer doest not take "merge append" 
   > cost into sql plan total cost and then make a bad sql plan. attached 
   > please find details.
   I suppose there is a different type of issue.
   MegeJoin sometimes doesn't need to scan the whole inner or outer side 
   (see the MergeScanSelCache structure and how it is used in the cost 
   estimation routine).
   
   So, the cost can be less because the optimizer predicted that only a 
   small part of the Append will be scanned and used some sort of 
   interpolation between startup cost and total cost.
   
   But to be sure, could you send the results of EXPLAIN ANALYZE VERBOSE? 
   If you also send the data to reproduce the case, we may find the source 
   of the problem more precisely.
   
   -- 
   regards, Andrei Lepikhov
   
   
   
   

3. Re: partition table optimizer join cost misestimation

   James Pang <jamespang886@gmail.com> — 2025-04-03T00:46:28Z

   Andrei,
      Yes, from explain output, since optimizer already get the merge_append
   cost but not take account into total cost, that make a big difference.  I
   shared table DDLs and explain analyze,buffers output , I think  the data
   maybe generated by other way to reproduce this issue. sorry for not sharing
   the commercial production data here.
   
   Andrei Lepikhov <lepihov@gmail.com> 於 2025年4月2日週三 下午7:03寫道：
   
   > On 4/2/25 12:18, James Pang wrote:
   > > Hi,
   > >     Postgresq v14.8, we found optimizer doest not take "merge append"
   > > cost into sql plan total cost and then make a bad sql plan. attached
   > > please find details.
   > I suppose there is a different type of issue.
   > MegeJoin sometimes doesn't need to scan the whole inner or outer side
   > (see the MergeScanSelCache structure and how it is used in the cost
   > estimation routine).
   >
   > So, the cost can be less because the optimizer predicted that only a
   > small part of the Append will be scanned and used some sort of
   > interpolation between startup cost and total cost.
   >
   > But to be sure, could you send the results of EXPLAIN ANALYZE VERBOSE?
   > If you also send the data to reproduce the case, we may find the source
   > of the problem more precisely.
   >
   > --
   > regards, Andrei Lepikhov
   >
   

4. Re: partition table optimizer join cost misestimation

   Andrei Lepikhov <lepihov@gmail.com> — 2025-04-03T06:32:44Z

   On 4/3/25 02:46, James Pang wrote:
   > Andrei,
   >     Yes, from explain output, since optimizer already get the 
   > merge_append cost but not take account into total cost, that make a big 
   > difference.  I shared table DDLs and explain analyze,buffers output , I 
   > think  the data maybe generated by other way to reproduce this issue. 
   > sorry for not sharing the commercial production data here.
   I think it is almost enough to identify the issue. Let me ask you the 
   following questions:
   1. Can you provide min and max values in columns cc.sss_id and aa.mmm_id?
   2. How often do you analyze your *parent* tables tablea and tablec? 
   Remember, if you want to build statistics on a partitioned table (not a 
   partition), you have to explicitly call
   
   ANALYZE tablea,tablec;
   
   mentioning these tables in the analyze list.
   
   3. May you provide a dump of pg_statistic on attributes cc.sss_id and 
   aa.mmm_id?
   4. Is there a possibility of changing a single code line and rebuilding 
   your DB instance to check a conjecture?
   
   -- 
   regards, Andrei Lepikhov
   
   
   
   

5. Re: partition table optimizer join cost misestimation

   James Pang <jamespang886@gmail.com> — 2025-04-03T12:38:49Z

     Follow your suggestion to increase statistics_target (I increase
   target_statistic to 1000 for  aa.mmm_id and cc.sss_id ,analyze tablea,
   tablec again),  optimizer choose the good SQL plan.
   
   Thanks,
   
   James
   
   Andrei Lepikhov <lepihov@gmail.com> 於 2025年4月3日週四 下午4:44寫道：
   
   > On 4/3/25 10:04, James Pang wrote:
   > > one more comments, for vacuum/analyze, we enable autovacuum=on, that may
   > > sometimes automatically analyze part of partition table directly.
   > I see some incoherence in data provided. The ranges of joining columns
   > intersects only partially:
   >
   > cc.sss_id: 5 100 001 101 - 7 999 999 601
   > aa.mmm_id: 2 005 242 651 - 5 726 786 022
   >
   > So, the intersection range 5100001101 - 5726786022 - is about 10% of the
   > whole range.
   > But I don't see it in the column statistics you provided. And Postgres
   > may do the same.
   > So, at first, I do recommend increasing default_statistics_target or
   > just statistics_target on partitioned tables only. For such big tables I
   > usually set it at least to the 2500.
   > Also, don't trust in autovacuum on partitioned table - to make an
   > analyse it needs to lock each partition which is highly unlikely to happen.
   > So, increase stat target, make ANALYZE tablea, tablec and let me know
   > what will happen. May be after the analyse statistics will be more
   > consistent.
   >
   > --
   > regards, Andrei Lepikhov
   >