pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStatistics on array values
7+ messages / 3 participants
[nested] [flat]
* Statistics on array values
@ 2020-02-02 14:18 Marco Colli <collimarco91@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Marco Colli @ 2020-02-02 14:18 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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...
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? Is there any solution or any plan to improve this in future
versions of PostgreSQL?
Finally it would be useful to have the ability to CREATE STATISTICS, to
show PostgreSQL that there's a correlation between project_id and tag
values... but this is a further step. Currently I can create statistics,
however it seems to have no positive effect on the estimates for the
above case
Marco Colli
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Statistics on array values
@ 2020-02-02 14:23 Justin Pryzby <pryzby@telsasoft.com>
parent: Marco Colli <collimarco91@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Justin Pryzby @ 2020-02-02 14:23 UTC (permalink / raw)
To: Marco Colli <collimarco91@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Statistics on array values
@ 2020-02-02 14:38 Marco Colli <collimarco91@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 7+ messages in thread
From: Marco Colli @ 2020-02-02 14:38 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
> Is one of those estimates way off reality, or is it only the conjunction
which is deranged?
The estimate is wrong *even with a single tag*, without the conjunction
(e.g. expected 3500, actual 20). Then the conjunction can make the bias
even worse...
On Sun, Feb 2, 2020 at 3:23 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Statistics on array values
@ 2020-02-02 17:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marco Colli <collimarco91@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2020-02-02 17:11 UTC (permalink / raw)
To: Marco Colli <collimarco91@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Statistics on array values
@ 2020-02-02 18:26 Marco Colli <collimarco91@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Marco Colli @ 2020-02-02 18:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
Thanks Tom for the clear explanation.
Unfortunately I don't get actual improvements. I use PG 11 and I run the
following commands:
ALTER TABLE subscriptions ALTER tags SET STATISTICS 1000;
ANALYZE subscriptions;
However the bias remains pretty much the same (slightly worse after). Any
idea?
On Sun, Feb 2, 2020 at 6:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Statistics on array values
@ 2020-02-02 18:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marco Colli <collimarco91@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2020-02-02 18:32 UTC (permalink / raw)
To: Marco Colli <collimarco91@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Marco Colli <collimarco91@gmail.com> writes:
> Unfortunately I don't get actual improvements. I use PG 11 and I run the
> following commands:
> ALTER TABLE subscriptions ALTER tags SET STATISTICS 1000;
> ANALYZE subscriptions;
> However the bias remains pretty much the same (slightly worse after). Any
> idea?
So what have you got in the pg_stats fields I asked about?
How big is this table anyway (how many rows, how many different tag
values)?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Statistics on array values
@ 2020-02-02 18:55 Marco Colli <collimarco91@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Marco Colli @ 2020-02-02 18:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
Sorry, I don't understand your exact question about pg_stats. In any case I
cannot make strict assumptions about data, because that greatly varies from
project to project (it's a SaaS) and over time. To give an idea the table
has some tens of millions of rows, each project has from a few thousands to
a few millions of rows and each project has its own tags that the customer
can define (unlimited tags for each row, but usually only 1 - 10 actually
used)
Il Dom 2 Feb 2020, 19:32 Tom Lane <tgl@sss.pgh.pa.us> ha scritto:
> Marco Colli <collimarco91@gmail.com> writes:
> > Unfortunately I don't get actual improvements. I use PG 11 and I run the
> > following commands:
> > ALTER TABLE subscriptions ALTER tags SET STATISTICS 1000;
> > ANALYZE subscriptions;
> > However the bias remains pretty much the same (slightly worse after). Any
> > idea?
>
> So what have you got in the pg_stats fields I asked about?
> How big is this table anyway (how many rows, how many different tag
> values)?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-02-02 18:55 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-02 14:18 Statistics on array values Marco Colli <collimarco91@gmail.com>
2020-02-02 14:23 ` Justin Pryzby <pryzby@telsasoft.com>
2020-02-02 14:38 ` Marco Colli <collimarco91@gmail.com>
2020-02-02 17:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-02-02 18:26 ` Marco Colli <collimarco91@gmail.com>
2020-02-02 18:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-02-02 18:55 ` Marco Colli <collimarco91@gmail.com>
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