problem with optimizing SQL query

Question

I'm noob when it's come to DBMS so i wrote a query on couple of relations and i want to know what's the best way to do it because i don't feel good about my query.

Here are the relations:

CREATE TABLE "Product" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "name" text,
  "unit_price" float8,
  "quantity" integer,
  "produce_per_day_ratio" integer,
  "sold_till_now" integer
);

CREATE TABLE "Order" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "personal_id" bigint,
  "product_id" bigint,
  "quantity" bigint,
  "company" text
);

CREATE TABLE "SalesManager" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "fName" text,
  "lName" text,
  "age" integer,
  "gender" bigint,
  "salary" bigint,
  "profit" float8
);

CREATE TABLE "Gender" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "name" text
);

ALTER TABLE "Order" ADD FOREIGN KEY ("personal_id") REFERENCES "SalesManager" ("id");

ALTER TABLE "Order" ADD FOREIGN KEY ("product_id") REFERENCES "Product" ("id");

ALTER TABLE "SalesManager" ADD FOREIGN KEY ("gender") REFERENCES "Gender" ("id");

So the question is "show the name of people who have sold the most products?" and by most sold i meant (quantity * unit_price)

EDIT:this is the updated query:

WITH orderProduct AS(
  SELECT 
    O.personal_id as pId , 
    sum(O.quantity * P.unit_price) as sold_amount,
    RANK() OVER (ORDER BY sum(O.quantity * P.unit_price) DESC) as rank
  FROM "Order" AS O
  INNER JOIN "Product" AS P ON O.product_id = P.id
  GROUP BY O.personal_id
)
SELECT concat("fName" , ' ' , "lName") as name , orderProduct.sold_amount
FROM "SalesManager"
INNER JOIN orderProduct ON id = pId
WHERE rank = 1;

So how bad is it ? :(

PS: i'm using postgres 12.2

Answer 1

One thing that can be improved is the choice of data types:

• for fields like company or product name varchar(50) (or a bit more) is sufficient. Text is overkill. You are not storing big chunks of text like forum posts.
• for age the realistic range would be 18-70. So smallint is more than enough (range: -32768 to +32767)
• usage of bigint is not justified in your schema - consider the range, it's totally overblown for your purpose:
  

bigint 8 bytes large-range integer -9223372036854775808 to +9223372036854775807

(source: PostgreSQL 9.5.21 Documentation - Chapter 8. Data Types)

• for gender: an enumerated type would be a good candidate because you are really expecting no more than a few options: Male/Female/Other.
• for unit_price you chose: float8, if I'm correct the usage range is: 1E-307 to 1E+308. That may be useful if you are trying to count the number of atoms in the universe but no item on Earth can be priced that much. It would be more reasonable to use NUMERIC and choose the precision you want, depending on how many digits you want to the right of the dot. For example retail items may need 2 decimals if you are pricing in cents, when buying in bulk from suppliers prices may be expressed with 4 decimals.
• ditto for salary

Here you are doing arithmetic operations like SUM so the performance is impacted by the data type although you may not notice it. On bigger datasets the impact could be more noticeable.

So I think you should review the data types available on that particular DBMS and redesign your tables with an effort to select the most appropriate data type for each field.

You are the designer and maybe the end user too, so you should know more or less what kinds of values are expected and the data types should be chosen accordingly, with some room for growth if you want but stay reasonable.

Since your question is about optimizing, another point that has not been addressed but that is strongly related to performance is indexing. If you join tables that have lots of records you will need indexes sooner or later, or retrieval times will become unacceptably slow. Good table design is important too.