From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marco Colli <collimarco91@gmail.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Statistics on array values
In-reply-to: 
 <CAFvCgN4d9G6=u-_jJPBZhTSdf1Uqo+-txwWdAp_hCDKXbBQOHw@mail.gmail.com>
References: 
 <CAFvCgN4d9G6=u-_jJPBZhTSdf1Uqo+-txwWdAp_hCDKXbBQOHw@mail.gmail.com>
Comments: In-reply-to Marco Colli <collimarco91@gmail.com>
	message dated "Sun, 02 Feb 2020 15:18:19 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2660.1580663465.1@sss.pgh.pa.us>
Date: Sun, 02 Feb 2020 12:11:05 -0500
Message-ID: <2661.1580663465@sss.pgh.pa.us>
Precedence: bulk

Marco Colli <collimarco91@gmail.com> writes:
> Let's say that you have a simple query like the following on a large table
> (for a multi-tenant application):
> SELECT "subscribers".* FROM "subscribers" WHERE "subscribers"."project_id"
> = 123 AND (tags @> ARRAY['de']::varchar[]);

> If you run EXPLAIN ANALYZE you can see that stats are completely wrong.
> For example I get an expected count of 3,500 rows whereas the actual
> result is 20 rows. This also results in bad query plans...

> In a previous discussion someone said that this wrong estimate is because
> @> uses a fixed selectivity of 0.001, **regardless of actual data**!!
> Is that true?

Hasn't been true since 9.2.

You might get some insight from looking into the most_common_elems,
most_common_elem_freqs, and elem_count_histogram fields of the pg_stats
view.

It seems likely to me that increasing the statistics target for this array
column would help.  IIRC, estimates for values that don't show up in
most_common_elems are going to depend on the lowest frequency that *does*
show up there ... so if you want better resolution for non-common values,
you need more entries.

			regards, tom lane