Subject: partition table optimizer join cost misestimation

From: James Pang <[email-masked]>
Date: Wed, 2 Apr 2025 18:18:57 +0800

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

---

From: Andrei Lepikhov <[email-masked]>
Date: Wed, 2 Apr 2025 13:03:37 +0200

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

---

From: James Pang <[email-masked]>
Date: Thu, 3 Apr 2025 08:46:28 +0800

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.

---

From: Andrei Lepikhov <[email-masked]>
Date: Thu, 3 Apr 2025 08:32:44 +0200

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

---

From: James Pang <[email-masked]>
Date: Thu, 3 Apr 2025 20:38:49 +0800

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 <[email-masked]> 於 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
>