Hello!
I have been struggling with finding a proper solution for this query for some time and wanted to ask if someone here knows how to approach this?
I have a table named "report" which has an index on report.reporter_id. This column consists of IDs which are grouped together using a table named "group_links".
So for every reporter id which is part of the same group, there is a row in "group_links" with the same group_id.
Now, I noticed that I can select reports for a group in two ways. Both queries return the same but one is using =ANY(ARRAY(expr)) ("subselect") and one is using =ANY(ARRAY) ("static array") with the same array as the _expression_ would return.
The static array query is running very fast for small selections and where not a lot of rows match the condition. It uses a bitmap index scan.
The subselect is running very slow and uses an index scan. However, it is particularly slow if not many rows match the condition and thus a lot of rows are filtered while scanning the index.
I was able to reproduce a similar issue with using `= ANY(VALUES)` instead of `= ANY(ARRAY)`:
1. fast query using =ANY(ARRAY): https://explain.depesz.com/s/dwP8
2. slow query using =ANY(ARRAY(expr)): https://explain.depesz.com/s/3hGb
3. slow query using =ANY(VALUES): https://explain.depesz.com/s/cYrn
I guess the difference comes from the query planner not being able to know the exact values for the WHERE condition beforehand. But how should cases like this be best handled?
Should I denormalize the data such that I have a table with columns report.id and group_id and report.created such that I can create an index on (created, group_id)? Then I don't have to do a subselect anymore.
I would be very glad for any help regarding this!
Postgres version: PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
------------------------------------
\d report
Table "public.report"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
reporter_id | uuid | | not null |
parsed | boolean | | |
id | text | | not null |
request_id | uuid | | |
created | timestamp with time zone | | not null | now()
customer | text | | |
subject | text | | |
parser_result | text | | not null |
parser | text | | |
event_types | jsonb | | |
event_count | integer | | |
account_id | integer | | |
reviewable | boolean | | not null | false
reviewed | boolean | | not null | false
Indexes:
"PK_99e4d0bea58cba73c57f935a546" PRIMARY KEY, btree (id)
"idx_report_created_desc_id_asc" btree (created DESC, id)
"idx_report_created_desc_reporter_id_asc" btree (created DESC, reporter_id)
"idx_report_event_types" gin (event_types)
"idx_report_parser_gin" gin (parser gin_trgm_ops)
"idx_report_parser_result_created_desc" btree (parser_result, created DESC)
"idx_report_reporter_id_asc_created_desc" btree (reporter_id, created DESC)
"idx_report_request_id_asc_created_desc" btree (request_id, created DESC)
"idx_report_subject_gin" gin (subject gin_trgm_ops)
Check constraints:
"report_parser_result_constraint" CHECK (parser_result = ANY (ARRAY['PARSED'::text, 'UNPARSED'::text, 'REJECTED'::text]))
Foreign-key constraints:
"FK_5b809608bb38d119333b69f65f9" FOREIGN KEY (request_id) REFERENCES request(id)
"FK_d41df66b60944992386ed47cf2e" FOREIGN KEY (reporter_id) REFERENCES reporter(id)
Referenced by:
TABLE "event" CONSTRAINT "event_report_id_foreign" FOREIGN KEY (report_id) REFERENCES report(id)
------------------------------------
SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname='report';
relname | relpages | reltuples | relallvisible | relkind | relnatts | relhassubclass | reloptions | pg_table_size
---------+----------+---------------+---------------+---------+----------+----------------+------------+---------------
report | 2062252 | 8.5893344e+07 | 2062193 | r | 22 | f | | 16898801664
(1 row)
------------------------------------
\d group_links
Table "public.group_links"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+-------------------
rule_id | uuid | | not null |
reporter_id | uuid | | not null |
group_id | uuid | | not null |
exclusion | boolean | | | false
last_update_time | timestamp with time zone | | | CURRENT_TIMESTAMP
Indexes:
"group_rules_matches_pkey" PRIMARY KEY, btree (rule_id, reporter_id)
"idx_group_rules_matches_group_id" btree (group_id)
"idx_group_rules_matches_group_id_reporter_id_exclusion" btree (group_id, reporter_id, exclusion)
"idx_group_rules_matches_reporter_id" btree (reporter_id)
Foreign-key constraints:
"group_rules_matches_group_id_foreign" FOREIGN KEY (group_id) REFERENCES "group"(id) ON DELETE CASCADE
"group_rules_matches_reporter_id_foreign" FOREIGN KEY (reporter_id) REFERENCES reporter(id)
"group_rules_matches_rule_id_foreign" FOREIGN KEY (rule_id) REFERENCES group_rules(id) ON DELETE CASCADE
------------------------------------
