Selecting parent and child data

I have an application front end which speaks to a PostgreSQL database. I'm trying to find the most efficient way to extract a structure similar to the below.

CREATE TABLE people (
  person_id SERIAL PRIMARY KEY,
  fname     VARCHAR
);

CREATE TABLE items (
  person_id INTEGER REFERENCES people (person_id),
  item_id   SERIAL PRIMARY KEY,
  title     VARCHAR
);

INSERT INTO people (fname) VALUES
  ('Bob'),
  ('Jim'),
  ('Geoff');

INSERT INTO items (person_id, title) VALUES
  (1, 'Cat'),
  (1, 'Dog'),
  (1, 'Monkey'),
  (2, 'Elephant');

My current approach would be to list all of the items, then (within the application) iterate over each and run a SELECT. Something similar to this Pseudo code:

items = db.Query(SELECT * FROM people);
for item in items
   itemsub = db.Query(SELECT * FROM items WHERE id = item.person_id)

There are a number of reasons why I don't like this.

• It requires a minimum of 1 + N(People) queries to extract a simple data structure. For a page containing 1000 items, this would produce a ton of network traffic.

-
The application has to manually iterate over the list atleast twice more after the database, one to build the better structure and a second to render it.

-
It seems like something a database should be able to do

One other alternative would be to perform a JOIN, but this would result in extra processing to remove duplicates in the application

The resulting structure should be relatively simple:

peopleItems{ 
   array people{ 
             array items{}
         }
}

However, I can't for the life of me work out the best approach.

This is just an idea.

test=# CREATE INDEX items_idx ON items (person_id);
CREATE INDEX

test=# SELECT person_id, fname,
(SELECT array_to_string(ARRAY(SELECT title FROM items WHERE items.person_id = people.person_id ), ',')) AS titles
FROM people;
person_id | fname | titles
-----------+-------+----------------
1 | Bob | Cat,Dog,Monkey
2 | Jim | Elephant
3 | Geoff |
(3 rows)

I don't know what application language you use (PHP, Ruby, or others), but all languages can easily parse this result.

I made dummy data and done EXPLAIN. The result is shown below:

test=# EXPLAIN
SELECT person_id,
fname,
(SELECT array_to_string(ARRAY(SELECT title FROM items WHERE items.person_id = people.person_id ), ',')) AS titles
FROM people;
QUERY PLAN

--------------------------------------------------------------------------------------
---
QUERY PLAN

--------------------------------------------------------------------------------------
-----
Seq Scan on people (cost=0.00..984256.31 rows=9990 width=8)
SubPlan 2
-> Result (cost=98.50..98.51 rows=1 width=0)
InitPlan 1 (returns $1)
-> Bitmap Heap Scan on items (cost=19.81..98.50 rows=455 width=16)
Recheck Cond: (person_id = people.person_id)
-> Bitmap Index Scan on items_idx (cost=0.00..19.70 rows=455 widt
h=0)
Index Cond: (person_id = people.person_id)
(8 rows)

This query uses Bitmap Index scan, so it can be run very fast.

Please don't forget to create index of person_id in the items table.

StackExchange Database Administrators Q#129476, answer score: 2