I am writing a query that returns a single record from the parent table. I would like to also return in this query if it has any children. This is a one to many relationship.

parent:
 -parent_id
 -name

child:
-child_id
-name
-parent_id

My first instinct is to write the following query:

select name, (select count(child_id) from child c  where c.parent_id=p.parent_id) children
     from parent p
     where name like 'some name'

But I was wondering if there was a more efficient way to do this, since I don't actually care about the count, just whether or not it has children. Any pointers?

  • 2
    Exactly. Use exists instead of counting all the children. I often liken this concept to someone asking how many people are in a room, versus asking whether or not the room is occupied. While knowing that there are 437 people in a room is nice, it's overkill if you're just looking for an empty room to use for a meeting... – Colin 't Hart Aug 17 at 19:12
  • @Colin'tHart good simile! – TheCatWhisperer Aug 17 at 21:09
up vote 6 down vote accepted

Don't forget that Postgres has a boolean datatype. The following is the most succinct way to express the query:

select
  parent_id,
  name,
  exists (select from child where parent_id = p.parent_id) as has_children
from parent p;

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=86748ba18ba8c0f31f1b77a74230f67b

  • Colin, wouldn't this still count all the children behind the scenes? – TheCatWhisperer Aug 17 at 19:30
  • 2
    No. Postgres will stop when it finds one record. If an index can be used to do this, it will use it. In your case, you should probably create an index on the foreign key column, child.parent_id, if you are using the query a lot, or if there are a lot of records involved. – Colin 't Hart Aug 17 at 19:37
  • all Fks are indexed ;) – TheCatWhisperer Aug 17 at 19:40
  • 1
    It’s fine. It will only perform the exists check for parents matching the where clause. – Colin 't Hart Aug 17 at 20:51
  • 1
    It continues to baffle me that the most expensive databases still lack this immensely useful data type. – jpmc26 Aug 18 at 3:51

Methods

Aggregate Method

The popular way we'll call it the aggregate method. Note bool_or(child_id IS NOT NULL) also works but was not any faster.

SELECT parent_id, count(*)>1 AS has_children
FROM parent
LEFT OUTER JOIN children
  USING (parent_id)
GROUP BY parent_id;

LEFT JOIN LATERAL with limit

But you may also try this, with LEFT JOIN LATERAL() like this..

SELECT parent_id, has_children
FROM parent AS p
LEFT JOIN LATERAL (
  SELECT true
  FROM children AS c
  WHERE c.parent_id = p.parent_id
  FETCH FIRST ROW ONLY
) AS t(has_children)
  ON (true);

EXISTS

Just FYI, you can use CROSS JOIN LATERAL with EXISTS too (which is I believe how it's planned). We'll call it the EXISTS method.

SELECT parent_id, has_children
FROM parent AS p
CROSS JOIN LATERAL (
  SELECT EXISTS(
    SELECT 
    FROM children AS c
    WHERE c.parent_id = p.parent_id
  )
) AS t(has_children);

Which is the same as,

SELECT parent_id, EXISTS(
    SELECT 
    FROM children AS c
    WHERE c.parent_id = p.parent_id
) AS has_children
FROM parent AS p;

Benchmarks

Sample dataset

1000000 children, 2500 parents. Our sims get it done.

CREATE TABLE parent (
  parent_id int PRIMARY KEY
);
INSERT INTO parent
  SELECT x
  FROM generate_series(1,1e4,4) AS gs(x);
CREATE TABLE children (
  child_id int PRIMARY KEY,
  parent_id int REFERENCES parent
);
INSERT INTO children
  SELECT x, 1 + (x::int%1e4)::int/4*4
  FROM generate_series(1,1e6) AS gs(x);

VACUUM FULL ANALYZE children;
VACUUM FULL ANALYZE parent;

Results (pt1)

  • Aggregate method: 450ms,
  • LEFT JOIN LATERAL ( FETCH FIRST ROW ONLY ): 850ms
  • EXISTS method: 850ms

Results (adding an index and running again)

Now let's add an index

CREATE INDEX ON children (parent_id);
ANALYZE children;

Now the timing profile is totally different,

  • Aggregate method: 450ms,
  • LEFT JOIN LATERAL ( FETCH FIRST ROW ONLY ): 30ms
  • EXISTS method: 30ms
  • 2
    Counting works, but using exists is more efficient. – Colin 't Hart Aug 17 at 19:10
  • That lateral trick is neat! But note that you can just use exists and a subquery as an expression as I do in my answer. – Colin 't Hart Aug 17 at 19:39
  • @Colin'tHart yep whatever syntax you'll prefer. – Evan Carroll Aug 17 at 19:55

This is how I would do in SQL server (I don't have postgresql- I'm guessing it would be similiar)

SELECT p.parent_id,
CASE WHEN EXISTS (SELECT 1 FROM Child c WHERE c.ParentId=p.ParentId)
                THEN 'Yes'
                ELSE 'No'
                END as has_child,
FROM Parent p
--WHERE EXISTS (SELECT 1 FROM Child c WHERE c.ParentId=p.ParentId)
  • 1
    The boolean datatype is a first-class citizen in Postgres, so you don't have to use case and distinct values in some other datatype to express the true and false values. Much better to leave it as a boolean and leave all presentation issues to the presentation layer, where it belongs. – Colin 't Hart Aug 17 at 19:43
  • 1
    Yes- like I mentioned in my answer- this IS how i would implement in SQL server. Interesting to know about the boolean in Postgres. – abbhey Aug 17 at 19:46

As long as there is index on parent_id column of child table your query should be good.

  • 4
    There's no need to count all the children, so there's definitely room for performance improvement. – Colin 't Hart Aug 17 at 19:10

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.