Statistics on array values

From: Marco Colli <collimarco91(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Statistics on array values
Date: 2020-02-02 14:18:19
Message-ID: CAFvCgN4d9G6=u-_jJPBZhTSdf1Uqo+-txwWdAp_hCDKXbBQOHw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Justin Pryzby 2020-02-02 14:23:21 Re: Statistics on array values
Previous Message Duncan Whitham 2020-01-31 11:01:20 Re: Specific query taking time to process