Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance - Mailing list pgsql-performance

From Ramdip Gill
Subject Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance
Date
Msg-id CAGg-0s_-KuTRUh7G2q4ELNH+EFsAq8feqbwdDYdVmKv0MtYhgg@mail.gmail.com
Whole thread Raw
In response to =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance  (Ramdip Gill <ramdip.singhgill@gmail.com>)
List pgsql-performance
Okay, increasing the collection of statistics seems to have helped. I used `ALTER TABLE report ALTER COLUMN reporter_id SET STATISTICS 10000` and now queries which previously didn't finish at all now finish in < 1 ms.

The following gave me the hint:

“The amount of information stored in `pg_statistic` by `ANALYZE`, in particular the maximum number of entries in the `most_common_vals` and `histogram_bounds` arrays for each column, can be set on a column-by-column basis using the `ALTER TABLE SET STATISTICS` command, or globally by setting the default_statistics_target configuration variable. The default limit is presently 100 entries. Raising the limit might allow more accurate planner estimates to be made, particularly for columns with irregular data distributions, at the price of consuming more space in `pg_statistic` and slightly more time to compute the estimates. Conversely, a lower limit might be sufficient for columns with simple data distributions.”

https://www.postgresql.org/docs/current/planner-stats.html

pgsql-performance by date:

Previous
From: Ramdip Gill
Date:
Subject: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance
Next
From: Rick Otten
Date:
Subject: Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance