Date: Sun, 2 Feb 2020 08:23:21 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Marco Colli <collimarco91@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Statistics on array values
Message-ID: <20200202142321.GH13621@telsasoft.com>
References: 
 <CAFvCgN4d9G6=u-_jJPBZhTSdf1Uqo+-txwWdAp_hCDKXbBQOHw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAFvCgN4d9G6=u-_jJPBZhTSdf1Uqo+-txwWdAp_hCDKXbBQOHw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

On Sun, Feb 02, 2020 at 03:18:19PM +0100, Marco Colli wrote:
> Hello!
> 
> 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...

https://www.postgresql.org/message-id/CAMkU%3D1z%2BQijUWAYgeqeyw%2BAvD7adPgOmEnY%2BOcTw6qDVFtD7cQ%40mail.gmail.com
On Fri, Jan 10, 2020 at 12:12:52PM -0500, Jeff Janes wrote:
> Why is the estimate off by so much?  If you run a simple select, what the
> actual and expected number of rows WHERE project_id = 12345?  WHERE tags @>
> '{crt:2018_11}'?  Is one of those estimates way off reality, or is it only
> the conjunction which is deranged?

Could you respond to Jeff's inquiry ?

Justin