From: Justin Pryzby <pryzby@telsasoft.com>
To: Marco Colli <collimarco91@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Statistics on array values
Date: Sun, 2 Feb 2020 08:23:21 -0600
Message-ID: <20200202142321.GH13621@telsasoft.com> (raw)
In-Reply-To: <CAFvCgN4d9G6=u-_jJPBZhTSdf1Uqo+-txwWdAp_hCDKXbBQOHw@mail.gmail.com>
References: <CAFvCgN4d9G6=u-_jJPBZhTSdf1Uqo+-txwWdAp_hCDKXbBQOHw@mail.gmail.com>
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%40...
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
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: pryzby@telsasoft.com, collimarco91@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Statistics on array values
In-Reply-To: <20200202142321.GH13621@telsasoft.com>
* 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