I have two tables person and tag. person has an bigint column containing ids of tags. I want to write a query which fetches all tags including the count of the tagged persons. So far I have the following query
SELECT t.id AS id, t.name AS name, (select count(id) from person where person.tags @> array[t.id]) as instances_count FROM tag AS t
I know the sub-query is bad but I couldn't think of a way of doing it better.
Any suggestions are welcome. Thank you
Edit: PostgreSQL 10.8
Here is the 'explain analyze':
Seq Scan on tag t (cost=0.00..4309761.27 rows=79 width=41) (actual time=131.109..11329.945 rows=79 loops=1) Buffers: shared hit=560506 read=3505230 SubPlan 1 -> Aggregate (cost=54553.91..54553.92 rows=1 width=8) (actual time=143.411..143.412 rows=1 loops=79) Buffers: shared hit=560505 read=3505230 -> Seq Scan on person i (cost=0.00..54550.82 rows=1233 width=8) (actual time=41.367..143.393 rows=148 loops=79) Filter: ((NOT is_deleted) AND (tags @> ARRAY[t.id])) Rows Removed by Filter: 245452 Buffers: shared hit=560505 read=3505230 Planning time: 0.098 ms Execution time: 11330.014 ms