From: Tom Lane <tgl@sss.pgh.pa.us>
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: Christoph Berg <myon@debian.org>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Surjective functional indexes
Date: Thu, 08 Nov 2018 18:27:15 -0500
Message-ID: <12290.1541719635@sss.pgh.pa.us> (raw)
In-Reply-To: <11218.1541718055@sss.pgh.pa.us>
References: <20181108190559.ylzufqr2jjvml32f@alvherre.pgsql>
<11218.1541718055@sss.pgh.pa.us>
I wrote:
> The bigger picture here, and the reason for my skepticism about having
> any intelligence in the enabling logic, is that there is no scenario
> in which this code can be smarter than the user about what to do.
> We have no insight today, and are unlikely to have any in future, about
> whether a specific index expression is many-to-one or not.
Hmm ... actually, I take that back. Since we're only interested in this
for expression indexes, we can expect that statistics will be available
for the expression index, at least for tables that have been around
long enough that UPDATE performance is really an exciting topic.
So you could imagine pulling up the stadistinct numbers for the index
column(s) and the underlying column(s), and enabling the optimization
when their ratio is less than $something. Figuring out how to merge
numbers for multiple columns might be tricky, but it's not going to be
completely fact-free. (I still think that the cost-estimate logic is
quite bogus, however.)
Another issue in all this is the cost of doing this work over again
after any relcache flush. Maybe we could move the responsibility
into ANALYZE?
BTW, the existing code appears to be prepared to enable this logic
if *any* index column is an expression, but surely we should do so
only if they are *all* expressions?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, k.knizhnik@postgrespro.ru, alvherre@2ndquadrant.com, laurenz.albe@cybertec.at, simon@2ndquadrant.com, robertmhaas@gmail.com, andres@anarazel.de, sfrost@snowman.net, michael.paquier@gmail.com, myon@debian.org, thomas.munro@enterprisedb.com, peter.eisentraut@2ndquadrant.com
Subject: Re: [HACKERS] Surjective functional indexes
In-Reply-To: <12290.1541719635@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox