From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexey Ermakov <alexey.ermakov@dataegret.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: rows selectivity overestimate for @> operator for arrays
Date: Fri, 27 May 2022 14:02:11 -0400
Message-ID: <2694646.1653674531@sss.pgh.pa.us> (raw)
In-Reply-To: <744e32e4-eb37-ad43-250b-11494b199a9b@dataegret.com>
References: <744e32e4-eb37-ad43-250b-11494b199a9b@dataegret.com>
Alexey Ermakov <alexey.ermakov@dataegret.com> writes:
> so if we store mostly one element in array and they're almost all
> distinct then in tables with more then stats_target/0.0063 (~1.58M for
> maximum stats target 10000) rows we'll get 0.005 constant for selectivity.
Yeah. There's a comment in array_selfuncs.c about
* TODO: this estimate probably could be improved by using the distinct
* elements count histogram. For example, excepting the special case of
* "column @> '{}'", we can multiply the calculated selectivity by the
* fraction of nonempty arrays in the column.
but I'm not sure whether that's relevant here.
One thought is that if there is a pg_statistic row but it contains
no MCE list, we could assume that the column elements are all distinct
and see what sort of estimate that leads us to.
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, alexey.ermakov@dataegret.com
Subject: Re: rows selectivity overestimate for @> operator for arrays
In-Reply-To: <2694646.1653674531@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