0

I have the following tables, where id is the unique identifier for records (but table D can't have it):

CREATE TABLE A (id int, a1 int, name text, a3 int);
INSERT INTO A VALUES (1, 100, 'Annie', 3);

CREATE TABLE B (id int, b1 int, b2 int);

CREATE TABLE C (id int, c1 int, c2 int);

CREATE TABLE D (d1 int, name text);
INSERT INTO D VALUES(2000, 'Annie');
INSERT INTO D VALUES(2050, 'Annie');

I need to find information related to the object whose id=1 from the above tables - b1, c2 and count(d1).
id is the only information known for the query (we don't know it's about "Annie", just that it's about an object with id=1). I know I can do something like:

SELECT A.id, A.name, B.b1, C.c2, dd.total
FROM A
LEFT JOIN B on A.id=B.id
LEFT JOIN C on A.id=C.id
LEFT JOIN (SELECT count(d1) total, name FROM D GROUP BY name) dd ON (dd.name = A.name)
WHERE A.id=1

Can I optimize this SQL? When tables grow large, doesn't the subquery SELECT count(d1) total, d2 FROM D GROUP BY d2 slows down the query a lot?

instant501
  • 195
  • 1
  • 10
  • 1
    Does the subquery slow your query down a lot or not? If you have 9999999 rows with "Annie" in the table then it is going to be slower than if you have 9 yes. But... how many rows do you have, running on what sort of hardware and how slow is the query? – Richard Huxton Jun 15 '23 at 08:52
  • @RichardHuxton I expect table D will contain lots of different names (Alice, Bob, ...) and each have 1-1500 records. I haven't actually run the query, just suspected that it might be slow and was wondering if I can come up with a better way to query – instant501 Jun 15 '23 at 09:24
  • 1
    You can't suspect anything without actually trying it with a reasonable amount of data and checking the query plans. It might well push the restriction on name into the subquery but if you only have 4 rows you are testing with the planner probably won't bother. – Richard Huxton Jun 15 '23 at 09:32
  • @RichardHuxton got it, thank you so much – instant501 Jun 16 '23 at 03:08

0 Answers0