lvorySQL 2025生态大会暨PostgreSQL高峰论坛：PostgreSQL 优化器缺陷：真实案例及修复方法

PostgreSQLOptimizerFlaws:RealStoriesand

HowWe

FixThemAlena

RybakinaHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceCoredeveloperin

Postgres

Professionalsince2021Contributingtothe

PostgreSQLprojectsince2023●

ORto

ANYtransformation●

Valuesto

ANYtransformation●

OthersParticipated

inextensiondevelopment:●

AQO●ReplaningHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceAbout

Me0

3The

PostgreSQLPlannerWith

Memory05SwitchJoin-

ChooseHashJoinOn

Time0

2TheCardinalityEstimation

Problem0

1HowTheOptimizerWorks0

4EndlessQuery

IssueOutlineofthetalk064.ConclusionHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceHowtheOptimizerWorksHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceProblemstatementGoal●Findthe

best

planamong

all

possible

ones

by

calculating

cost●Basedonthecost-based

model

introduced

by

System

R

(1978–1979)Problem●Thenumberofpossible

plansgrows

exponentiallywith

the

number

oftables:~exp(numberoftables)ApproximateSolution●

Usesdynamic

programmingalgorithmSearch●Aplan

isguaranteedto

befound,

but

it

might

not

betheoptimal

oneHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceWhat

IsThe

PlanCostPlancost=

cost

of

plan

componentsCPUparametersCardinalitySelectivityHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceWhat

IsThe

PlanCostPlancost=

cost

of

plan

componentsCPUparametersCardinalitySelectivityHOW2025PostgreSQL

&

IvorySQL

Eco

Conference|rowsTAB1

|

=

3|rowsTAB2

|

=

3|rowsTAB1xTAB2

|

=

9Cardinality–thenumberoftuples(rows)producedafter

executing

an

operation.WhatAreCardinalityandSelectivityHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceCardinality–thenumberoftuples(rows)producedafter

executing

an

operation.Selectivity–thefractionofrowsfromtheCartesianproduct

oftwo

setsthat

remains

afterthejoinconditionis

applied.|rowsTAB1

|

=

3|rowsTAB2

|

=

3|

BI

|x

|

=

9selJOIN=

1/3TA=1NrowsTArowsJOWhatAreCardinalityandSelectivityHOW2025PostgreSQL

&

IvorySQL

Eco

Conference|rowsTAB1

|

=

3|

BI

|x

|

=

9selJOIN=

1/3TA=1NrowsTArowsJO|rowsTAB2

|

=

3Cardinality–thenumberoftuples(rows)producedafter

executing

an

operation.Selectivity–thefractionofrowsfromtheCartesianproduct

oftwo

setsthat

remains

afterthejoin

conditionis

applied.Wecan't

predict

it

likethis!WhatAreCardinalityandSelectivityHOW2025PostgreSQL

&

IvorySQL

Eco

Conferencemostcommonvalues

(MCV)Usedwhenthenumberofdistinctvalues

is

notvery

largeFractionof

NULLs

(null_frac)Representstheproportionofmissing

(NULL)valuesHistogramsUsedwhenthenumberofdistinct

values

is

largeHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceStatisticsMCV:-most_common_values-most_common_frequencesSELECTmost_common_vals,

most_common_freqsFROM

pg_statsWHEREtablename

=

'startups'

AND

attname

=

'country'States",India,China,

.

.

.}most_common_freqs

|{0.515,0.121,0.117,0.053…}attnamenull_fracmost_common_valsStatistics:

MostCommonValues|

country

|

0|{"UnitedHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceStatistics:

Histogrampostgres=#SELECTattname,histogram_boundsFROM

pg_statsWHERE

tablename

='startups'

ORattname

=

'country'ORattname=

'company'\gx-[

RECORD

1]+

attname

|countryhistogram_bounds

|-[

RECORD

2]+

attname

|companymost_common_vals

|most_common_freqs

|null_frac

|

0histogram_bounds|{AQOrns,Addepar,Airtable,"AmWINSGroup"

.

.

.}HOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceSELECT

count(*)

from

startups

WHERE

valuation

<

50;attname

|

valuationn_distinct

|-0.7196262most_common_vals

|

{4,3.7,2.75,3.1,

.

.

.}

-35

values

matching

the

condition

most_common_freqs|

{0.032,0.028,0.019,0.019,0.019,0.019,0.014,.

.

.}histogram_bounds|{

.

.

.,18,20,30,40,50,60,80,90,100,110,120,140}How

IsCardinality

Estimated

in

PostgreSQLHOW2025PostgreSQL

&

IvorySQL

Eco

Conference

93SELECT

count(*)

from

startups

WHERE

valuation

<

50;attname

|

valuationn_distinct

|-0.7196262most_common_vals

|

{4,3.7,2.75,3.1,

.

.

.}

-35

values

matching

the

condition

most_common_freqs|

{0.032,0.028,0.019,0.019,0.019,0.019,0.014,.

.

.}histogram_bounds|{

.

.

.,18,20,30,40,50,60,80,90,100,110,120,140}SumMCV_FREQ(<

50)

=

0.444NDistinct_tuples=NTuples(217)*0.7196=

156How

IsCardinality

Estimated

in

PostgreSQLHOW2025PostgreSQL

&

IvorySQL

Eco

Conference

93SELECT

count(*)

from

startups

WHERE

valuation

<

50;attname

|

valuationn_distinct

|-0.7196262most_common_vals

|

{4,3.7,2.75,3.1,

.

.

.}

-35

values

matching

the

condition

most_common_freqs|

{0.032,0.028,0.019,0.019,0.019,0.019,0.014,.

.

.}histogram_bounds|{

.

.

.,18,20,30,40,50,60,80,90,100,110,120,140}SumMCV_FREQ(<

50)

=

0.444NDistinct_tuples=NTuples(217)*0.7196=

156Selectivity_hyst（<

50)

=

bound[i]/(NBOUNDS

-

1)

-

1/

(N

DISTINCT

-NMCV)

=

=(93

/

(101-1)-1

/

(156

-35)

=

0.922How

IsCardinality

Estimated

in

PostgreSQLHOW2025PostgreSQL

&

IvorySQL

Eco

Conference

93SELECT

count(*)

from

startups

WHERE

valuation

<

50;attname

|

valuationn_distinct

|-0.7196262most_common_vals

|

{4,3.7,2.75,3.1,

.

.

.}

-35

values

matching

the

condition

most_common_freqs|

{0.032,0.028,0.019,0.019,0.019,0.019,0.014,.

.

.}histogram_bounds|{

.

.

.,18,20,30,40,50,60,80,90,100,110,120,140}SumMCV_FREQ(<

50)

=

0.444NDistinct_tuples=NTuples(217)*0.7196=

156Selectivity_hyst（<

50)

=

bound[i]/(NBOUNDS

-

1)

-

1/

(N

DISTINCT

-NMCV)

==(93

/

(101-1)-1

/

(156-35)

=

0.922Selectivity

=(1

-

null_frac)

*

SELECTIVITYHYST+

SUMMCV_FREQ==(1-

0.44393)

*

0.922

+

0.444=0.956How

IsCardinality

Estimated

in

PostgreSQLHOW2025PostgreSQL

&

IvorySQL

Eco

Conference

93SELECT

count(*)

from

startups

WHERE

valuation

<

50;attname

|

valuationn_distinct

|-0.7196262most_common_vals

|

{4,3.7,2.75,3.1,

.

.

.}

-35

values

matching

the

condition

most_common_freqs|

{0.032,0.028,0.019,0.019,0.019,0.019,0.014,.

.

.}histogram_bounds|{

.

.

.,18,20,30,40,50,60,80,90,100,110,120,140}SumMCV_FREQ(<

50)

=

0.444NDistinct_tuples=NTuples(217)*0.7196=

156Selectivity_hyst（<

50)

=

bound[i]/(NBOUNDS

-

1)

-

1/

(N

DISTINCT

-NMCV)

==(93

/

(101-1)-1

/

(156-35)

=

0.922Selectivity

=(1

-

null_frac)

*

SELECTIVITYHYST+

SUMMCV_FREQ==(1-

0.44393)

*

0.922

+

0.444=0.956Cardinality

=

NTuples*Selectivity=NTuples(217)*0.956=207.45How

IsCardinality

Estimated

in

PostgreSQLHOW2025PostgreSQL

&

IvorySQL

Eco

Conference

93Selectivity_hyst（<

50)

=

bound[i]/(NBOUNDS

-

1)

-

1/

(NDISTINCT

-

NMCV)Selectivity

=(1

-

null_frac)

*

SELECTIVITYHYST

+

SUMMCV_FREQ

Cardinality

=

NTuples

*

SelectivitySELECT

count(*)

from

startups

WHERE

valuation

<

50;Theoptimizerassumesauniformdistributionofdataacrosshistogram

bins.Aggregate(cost=7

.19

.

.7

.20

rows=1width=8)Seq

Scan

on

best_cities_for_startaps

(rows=207

width=0)Filter:(valuation

<'50'::double

precision)How

IsCardinality

Estimated

in

PostgreSQLHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceSelectivity_hyst（<

50)

=

bound[i]/(NBOUNDS

-

1)

-

1/

(NDISTINCT

-

NMCV)Selectivity

=(1

-

null_frac)

*

SELECTIVITYHYST

+

SUMMCV_FREQ

Cardinality

=

NTuples

*

SelectivitySELECT

count(*)

from

startups

WHERE

valuation

<

50;Theoptimizerdoes

notcorrectlyaccountforthepresenceof

NULLsintroducedby

outer

joinsAggregate(cost=7

.19

.

.7

.20

rows=1width=8)Seq

Scan

on

best_cities_for_startaps

(rows=207

width=0)Filter:(valuation

<'50'::double

precision)How

IsCardinality

Estimated

in

PostgreSQLHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceAggregate(rows=1)Seq

Scan

on

best_cities_for_startaps

(rows=1)Filter:(valuation

<'50'::double

precision)SelectivityValuation

and

country

=SelectivityValuation

*

SelectivityСountryWith

the

only

following

exception:Selectivity

25<value

AND

value<57=

Selectivity

25<value<57Theclausesare

always

consideredto

beindependentby

the

optimizerSELECT

count(*)

from

startups

WHERE

valuation

<50

and

country=

'China';How

IsCardinality

Estimated

in

PostgreSQLHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceTotal

Cost:170.0Plancost=

cost

of

plan

componentsThecostofasingle

nodedependsonthetype

ofthe

node

and

thevolumeofdata

it

processes.SeqScan

tbl1:●cpu_run_cost:○cpu_tuple_cost*Ntuples○cpu_operator_cost*Ntuples

●disk_run_cost:○seq_page_cost*NpagesNpages:45Ntuples:

10000What

IsThe

PlanCostHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceParse

ParserHowTheOptimizerWorksPlain

Text

SQL

System

CatalogHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceAnalyzerTreeRewriterParseStatement

Statement

SQLQuery

Parse

ParserHowTheOptimizerWorksPlain

Text

SQL

System

CatalogHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceAnalyzerTreeRewriterParseStatement

PathGeneratorStatement

SQLQuery

QueryTreePathTree

Generator

RewriterSQLQuery

PlainText

SQLParse

ParserAnalyzerParseStatement

Statement

Expressions

cardinalityHowTheOptimizerWorks

QueryTree

Optimal

Path

HOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceCardinality

Estimator

Cost

EstimatorSystemCatalogPlainText

SQLSQLQuery

TreeRewriterParse

ParserParseStatementStatement

AnalyzerExpressions

cardinalityHowTheOptimizerWorksQuery

ExecutorHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceCardinality

Estimator

Cost

EstimatorPathGeneratorPlanGeneratorOptimal

Path

Query

PlanSystemCatalogQueryTreeResultsStatement

PlainText

SQLSQLQuery

Parse

AnalyzerParserParseStatement

TreeRewriterPathExpressions

cardinalityHowTheOptimizerWorksHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceCardinality

Estimator

Cost

EstimatorPlanQuery

Executor

SystemCatalogOptimal

PathQuery

PlanQueryTreeGeneratorGeneratorResultsSQLQuery

PlainText

SQLParseStatementParserParseStatementAnalyzerTreeRewriterPathExpressionsWhatcouldpossibly

gowrong?HOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceCardinality

Estimator

Cost

EstimatorPlanQuery

Executor

SystemCatalogcardinalityOptimal

PathQuery

PlanQueryTreeGeneratorGeneratorResultsPlainText

SQLSQLQuery

ParserParseStatementParseStatementAnalyzerTreeRewriterWhyare

myqueriessoslow?ExpressionsPathWhatcouldpossibly

gowrong?HOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceCardinality

Estimator

Cost

EstimatorPlanQuery

Executor

SystemCatalogcardinalityOptimal

PathQuery

PlanQueryTreeGeneratorGeneratorResultsTheCardinality

Estimation

ProblemHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceThe

ReasonsofCardinality

Estimation

Problem●

Theuseofoutdated

statistics●Non-uniformdatadistribution●Havingcorrelation

betweencolumns●Havingofunaccountedzeroelementsarisingfroman

outer

join●Inabilitytousestatistics

after

aggregationHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceThe

ReasonsofCardinality

Estimation

Problem●

Theuseofoutdated

statistics●Non-uniformdatadistribution●Havingcorrelation

betweencolumns●Havingofunaccountedzeroelementsarisingfroman

outer

join●Inabilitytousestatistics

after

aggregationThe

main

problem

istheplannerdoesn'tlearn

on

itsmistakes!HOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceThe

PostgreSQLPlannerWith

MemoryHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceThe

Memory

ForPlanner(AQO)●

AQO

isan

extension●

Theoptimizercanstoretheactualselectivityof

relations

and

passes

itto

the

optimizernext

time●

Theoptimizerusesactualselectivity

information

inthe

nexttimeofquery

plangenerationofthisquery●

Theoptimizerlearnsfrom

mistakesandcorrectsdata

using

Machine

Learning

It

is

the

main

idea

thatAQO

is

basedon!HOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceItstoresselectivitiesforsome

Relations(Nodes)ofqueries.

All

nodes

have

unique

hash.The

Memory

ForPlanner(AQO)HOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceItstoresselectivitiesforsome

Relations(Nodes)ofqueries.

All

nodes

have

unique

hash.

Relation(RelOptInfo

nodes)Base

RelationRepresentsa

realtable,view,ora

subquery

intheFROM

clause.UPPERREL_GROUP_AGGrepresentsthe

resultofapplyingGROUP

BYandaggregationoperationsonan

existing

relationJoin

RelationRepresentsa

logicaljoin

betweentwoor

morerelations.The

Memory

ForPlanner(AQO)HOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceItstoresselectivitiesforsome

Relations(Nodes)ofqueries.

All

nodes

have

unique

hash.Plan

Nodes

representthe

physicaloperations

usedtoaccessand

processrelations.Others(likeWindow

Function,Distinct,Append,Ordered,...)The

Memory

ForPlanner(AQO)Base

RelationSeqScanIndexScanIndexBitMapScanUPPERREL_GROUP_AGGGroup

ByJoin

RelationNestedLoopHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceHashJoinMergeJoinHash

AggregateTheSchemaofTheOptimizerPathGeneratorPlanGeneratorQueryTreeOptimal

PathQuery

PlanResultsQuery

ExecutorHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceCardinality

EstimatorSystemCatalogcardinalityCost

EstimatorWhen

the

executorhasfinishedexecutingthequery,AQO

examines

theactualcardinalitiesofthe

nodesPathGeneratorPlanGeneratorHowTheOptimizerWorksQueryTreeOptimal

PathQuery

PlanResultsQuery

Executorfeatures,actualcardinalitiesHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceCardinality

EstimatorSystemCatalogcardinalityCost

EstimatorLearningAQuery

ExecutorPathGeneratorPlanGeneratorCost

Estimator

cardinality

Itcalculates

newcardinalitybased

ontheerror

betweenactualand

predictedcardinalityandupdates

it

inAQO_dataLearningNewfeatures,newcardinalities

features,HowTheOptimizerWorksQueryTreeOptimal

PathQuery

PlanResultsHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceactualcardinalitiesCardinality

EstimatorSystemCatalogQuery

ExecutorPathGeneratorPlanGeneratorIt

usesthe

actualcardinalitiesduringthe

nextqueryplanningphase

forthisquery.Cost

EstimatorHowTheOptimizerWorksQueryTreeOptimal

PathQuery

PlanResultsfeatures,actualcardinalitiesNewfeatures,newcardinalitiesHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceCardinality

EstimatorSystemCatalogcardinalityCost

EstimatorLearningAHash

Cond:(large

.id=

small.id)Filter:

(small.b

IS

NULL)->

Parallel

Seq

Scan

onlarge(rows=416667)(actualtime=0

.105

.

.45

.022rows=333333

loops=3)->

Hash(rows=100)(actual

time=0

.162

.

.0

.163

rows=100

loops=3)...CREATE

TABLE

large

AS

SELECT

i

as

id,

i

as

a

FROM

generate_series(1,1000000)

AS

s(i);CREATE

TABLE

small

AS

SELECT

i

as

id,i

as

b

FROM

generate_series(1,100)

AS

s(i);ANALYZE;SELECT*

FROM

large

LEFT

JOIN

small

ON

large.id

=

small.idWHEREsmall.bISNULL;Gather(rows=1)(actualtime=0

.841

.

.220

.948

rows=999900loops=1)->

HashLeftJoin(rows=1)(actualtime=1

.118

.

.144

.914

rows=333300)Null

Propagation

MisestimationHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceSELECT*

FROM

large

LEFT

JOIN

small

ON

large.id

=

small.id

WHEREsmall.b

IS

NULL;Hash

Left

Join(rows=999900)(actual

time=0

.367

.

.369

.565

rows=999900

loops=1)AQO:rows=999900,error=0%,

fss=-7000922901855416540Hash

Cond:

(large.id

=

small.id)Filter:

(small.b

IS

NULL)Rows

Removed

by

Filter:

100->

Seq

Scan

on

large(rows=1000000)

(actual

time=0

.115

.

.116

.583rows=1000000loops=1)AQO:rows=1000000,error=0%,fss=-3805682035012663455->

Hash(rows=100)(actual

time=0

.113

.

.0

.115

rows=100

loops=1)Buckets:1024Batches:

1Memory

Usage:

12kB->

SeqScanonsmall(rows=100)

(actualtime=0

.019

.

.0

.047rows=100loops=1)AQO:rows=100,error=0%,

fss=-4469147431455716353Null

Propagation

MisestimationHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceSELECT*

FROM

large

LEFT

JOIN

small

ON

large.id

=

small.id

WHEREsmall.b

IS

NULL;Gather(rows=1)(actualtime=0

.841

.

.220

.948

rows=999900loops=1)->

HashLeftJoin(rows=1)(actualtime=1

.118

.

.144

.914

rows=333300

)Hash

Cond:

(large.id

=

small.id)Filter:

(small.b

IS

NULL)●

Small.b

IS

NULL:we

needall

unmatchedtuples●

Only

100

large.idvalues

matchanysmall.id●

999,900

rowsofthe

LEFTJOINwill

havesmall.b=

NULL●Butthe

plannerestimates~0

rows

because

itchecksthestatsofsmall.b●

The

plannerdoesn't

realizethis

IS

NULLisfroma

non-match,

notfromactual

nulls

inthecolumn.Null

Propagation

MisestimationHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceSELECT*

FROM

large

LEFT

JOIN

small

ON

large.id

=

small.id

WHEREsmall.b

IS

NULL;Gather(rows=1)(actualtime=0

.841

.

.220

.948

rows=999900loops=1)->

HashLeftJoin(rows=1)(actualtime=1

.118

.

.144

.914

rows=333300)Hash

Cond:

(large.id

=

small.id)Filter:

(small.b

IS

NULL)Thread

in

PostgreSQL

Hackers:Problems

with

estimating

OR

conditions,

IS

NULLon

LEFTJOINsNull

Propagation

MisestimationHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceReal

ExampleWith

UnderestimationCardinalityEXPLAIN

ANALYZE

SELECTT1

.Fld1,MAX(T1.Fld2),T1

.Fld7,MAX(T7.Fld8),CASE

WHEN

(T13.Fld9

=

'\\202k\\

.

.

.v'::bytea)

THEN

TRUE

ELSE

FALSE

END,

MAX(CASEWHEN(T1.Fld3

IN('\\201\\

.

.

.'::bytea,'\\201///'::bytea))

THENTRUEELSE

FALSE

END),CASE

WHEN

(T13.Fld9

=

'\\202k\\

.

.

.v'::bytea)

THEN00:00:00'::timestampEND,PlanningTime:49.113

msExecutionTime:4155664.501T13

.Period_ELSE'0001-01-01MAX(T1

.Fld1_type)

.

.

.200

linesofsql

queryHOW2025PostgreSQL

&

IvorySQL

Eco

ConferencemsGroupAggregate(rows=1)(actual

time=4155412

.420

.

.4155658

.870

rows=355

loops=1)GroupKey:t6._fld3841,.

.

.

,

(COALESCE(t12.fld1,

false)),

(CASE

WHEN

(t16

.fld2

='\\x816

.

.

.'::bytea)THENtrue

ELSE

false

END)

.

.

.Filter:

(max(t10._fld5072rref)

='\\xa6

.

.

.'::bytea)Sort(rows=1)(actual

time=4155405

.791

.

.4155409

.084

rows=17709loops=1)NestedLoopLeftJoin(rows=1)(actual

time=0

.773

.

.4155299

.068rows=17709)...5Nested

LoopsNestedLoop(rows=1)(actual

time=0

.418

.

.1858

.343

rows=17705loops=1)MergeJoin(rows=1)(actualtime=0

.361

.

.445

.920

rows=17812loops=1)

GroupAggregate(rows=14381)(actualtime=0

.068

.

.129

.964rows=17705)Group

Key:

t9._fld13,

t9._fld14,...Index

Only

Scan(rows=31345)

(rows=31292)Planning

Time:

49.113msExecution

Time:4155664.501

ms~69.2611minsThe

plannercan't

relyonstatisticsafteranaggregationfunction,as

it

typically

produces

a

non-uniformdatadistribution.Real

ExampleWith

UnderestimationCardinalityHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceGroupAggregate(rows=1)(actual

time=4155412

.420

.

.4155658

.870

rows=355

loops=1)GroupKey:t6._fld3841,.

.

.

,

(COALESCE(t12.fld1,

false)),

(CASE

WHEN

(t16.fld2

='\\x816

.

.

.'::bytea)THENtrue

ELSE

false

END)

.

.

.Filter:

(max(t10._fld5072rref)

='\\xa6

.

.

.'::bytea)Sort(rows=1)(actual

time=4155405

.791

.

.4155409

.084

rows=17709loops=1)NestedLoopLeftJoin(rows=1)(actual

time=0

.773

.

.4155299

.068rows=17709)...5Nested

LoopsNestedLoop(rows=1)(actual

time=0

.418

.

.1858

.343

rows=17705loops=1)MergeJoin(rows=1)(actualtime=0

.361

.

.445

.920

rows=17812loops=1)GroupAggregate(rows=14381)(actualtime=0

.068

.

.129

.964rows=17705)Group

Key:

t9._fld13,

t9._fld14,...Index

Only

Scan(rows=31345)

(rows=31292)Planning

Time:

49.113msExecution

Time:4155664.501

ms~69.2611minsAnd

it

leadstosignificant

underestimation

problemandthe

plannerprefers

using

several

Nested

LoopsReal

ExampleWith

UnderestimationCardinalityHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceGroupAggregate(rows=1)(actual

time=4155412

.420

.

.4155658

.870

rows=355

loops=1)GroupKey:t6._fld3841,.

.

.

,

(COALESCE(t12.fld1,

false)),

(CASE

WHEN

(t16.fld2

=

'\\x816

.

.

.'::bytea)THENtrue

ELSE

false

END)

.

.

.Filter:

(max(t10._fld5072rref)

='\\xa6

.

.

.'::bytea)Sort(rows=1)(actual

time=4155405

.791

.

.4155409

.084

rows=17709loops=1)NestedLoopLeftJoin(rows=1)(actual

time=0

.773

.

.4155299

.068rows=17709)...5Nested

LoopsNestedLoop(rows=1)(actual

time=0

.418

.

.1858

.343

rows=17705loops=1)MergeJoin(rows=1)(actualtime=0

.361

.

.445

.920

rows=17812loops=1)GroupAggregate(rows=14381)(actualtime=0

.068

.

.129

.964rows=17705)Group

Key:

t9._fld13,

t9._fld14,...Index

Only

Scan(rows=31345)

(rows=31292)Planning

Time:

49.113msReal

ExampleWith

UnderestimationCardinalityThis

leadstothequerytakingtoo

longtoexecute.Execution

Time:4155664.501

ms~69.2611minsHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceHashRightJoin(rows=17709)

(actualrows=17709)HashJoin(rows=17812)

(actual

rows=17812)Seq

Scan

on_inforg3838t24(rows=68286)

(rows=68286)Hash(rows=16374)

(actual

rows=17812)HashAggregate(rows=16374)

(rows=17812)Hash

Join(rows=17812)

(rows=17812)AQO:rows=17812,error=0%,

fss=-893756562HashAggregate(rows=17812)

(rows=17812)Planning

Time:15177.976msExecution

Time:2891.435ms

Query

hash:4602769443974248783HashAggregate(rows=2)(actual

rows=355

loops=1)AQO:rows=355,

error=0%,

fss=82869432(rows=17709)(actual

rows=17709loops=1)HashJoin(rows=17709)

(actualrows=17709loops=1)HashJoinLeftHOW2025PostgreSQL

&

IvorySQL

Eco

ConferenceFixWithAQOAdaptiveQueryOptimizer●

AQO

isanextension●

Theoptimizercanstoretheactual

selectivity

of

relationsand

passes

ittotheoptimizernext

time●

Theop