Re: Using multiple extended statistics for estimates

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Mark Dilger <hornschnorter(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Using multiple extended statistics for estimates
Date: 2019-11-13 15:28:23
Message-ID: 20191113152823.jdpertc73gfo2mk7@development
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


here's an updated patch, with some minor tweaks based on the review and
added tests (I ended up reworking those a bit, to make them more like
the existing ones).

There's also a new piece, dealing with functional dependencies. Until
now we did the same thing as for MCV lists - we picketd the "best"
extended statistics (with functional dependencies built) and just used
that. At first I thought we might simply do the same loop as for MCV
lists, but that does not really make sense because we might end up
applying "weaker" dependency first.

Say for example we have table with columns (a,b,c,d,e) and functional
dependencies on (a,b,c,d) and (c,d,e) where all the dependencies on
(a,b,c,d) are weaker than (c,d => e). In a query with clauses on all
attributes this is guaranteed to apply all dependencies from the first
statistic first, which si clearly wrong.

So what this does instead is simply merging all the dependencies from
all the relevant stats, and treating them as a single collection.


Tomas Vondra
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
0001-Apply-multiple-multivariate-MCV-lists-when-possible.patch text/plain 13.2 KB
0002-Apply-all-available-functional-dependencies.patch text/plain 10.5 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Lætitia Avrot 2019-11-13 15:48:57 Re: [Doc] pg_restore documentation didn't explain how to use connection string
Previous Message Tom Lane 2019-11-13 14:47:01 Re: Invisible PROMPT2