Currently I have a query in PostgreSQL that makes use of the ARRAY_AGG function in order to group together all distinct values for each column.
The query works fine however it can take up to 38 seconds to run which is not that efficient.
The query currently is like this:
WITH agged_data AS (
SELECT birth_date, place_of_birth, first_name
FROM user_info
)
SELECT (ARRAY_AGG(DISTINCT birth_date)),
(ARRAY_AGG(DISTINCT place_of_birth)),
(ARRAY_AGG(DISTINCT first_name)),
FROM agged_data LIMIT 100
There are around >17,000 rows.
The query gets executed in Python backend (FastAPI) but I tested it out in PgAdmin as well. Originally I was running separate queries for each column (there are more columns in the table and the query I use, I just wrote the above as a MWE). But then thought it would put more strain on the db.
Is there an alternative to ARRAY_AGG for what I want to achieve?
LIMIT 100inside theagged_dataquery?array_aggalways returns a single row anyway.ARRAYconstructor:SELECT ARRAY(SELECT DISTINCT birth_date FROM user_info LIMIT 100) AS birth_dates, ARRAY(…) AS places_of_birth, …;