Postgresql query takes unnecessary (?) long time

Question

I know the web is full of questions, complaints, etc. like my question. But each of them still does not answer the questions of people finding those posts via Google, ...

I try to post my question with an example but would like to find some generic advices for possible reasons for this kind of problems.

I have a table of this structure (there are some foreign keys)

id uuid [uuid_generate_v4()] 
locationId uuid NULL 
orderItemId uuid NULL 
createdAt timestamptz 
amount numeric(10,4) NULL [0] 
quantity integer NULL 
productId uuid NULL 
bookingId uuid NULL 
courseId uuid NULL 
courseUnitId uuid NULL 
courseModuleId uuid NULL 
courseCategoryId uuid NULL 
courseCategoryIds jsonb NULL [[]] 
kind character varying 
discountRuleId uuid NULL 
inquiryId uuid NULL

The table is filled with about 3000 rows (which seems not much to me).

But when I run this query

select * from stat_record

the result is provided after about 20 seconds. If i decrease the number of selected fields (like ```select id, "locationId from stat_record") the processing time increases linear-relative - I double the amount of selected fields, the waiting time will be doubled also approximately)

What could be potential reasons for this?

1. Can indizes help in this case? Or are indizes only helping with performance while joining over tables?
2. Damaged/inconsitent tables? What is a good starting point to learn about VACUUM, INDEX, REINDEX?
3. ... ?

Execution plan: explain (analyze, buffers, format text) select * from stat_record gives

Seq Scan on stat_record (cost=0.00..122.12 rows=3812 width=225) (actual time=0.011..0.636 rows=3812 loops=1)
 Buffers: shared hit=84
Planning Time: 0.102 ms
Execution Time: 0.998 ms

• ILCAI: No, the jsonb values are not even present. If I omit that one field from the select statement, there is no significant change to the amount of time it takes.
• user1822: The (wrongly formatted) plan shows that the query is execute in 0.3 milliseconds. That's 0.0003 seconds. If you see a duration of 20 seconds in your SQL tool, it's caused by either the network (latency, speed) or by your SQL client.
• user1822: The result of explain looks like this It's not some JSON array that loses the indention and thus vital information about the structure of the plan.
• ILCAI: ... ah alright. seems to be normal for using this tool. cause every line correspondents to a row which has the selected field "QUERY PLAN". Got you. Thank you, I will investigate further, perhaps it is a TypeORM specific problem, which I will report here.
• user1822: No. The plan clearly shows that the query itself is very fast. If it happens with different SQL clients, it's likely something in your network.

Answer 1

By analysing the planning and execution time (thank you, @a_horse_with_no_name), it seems clear, that the query does not take long at all.

The problem was, I am using an ORM, which obviously drops the performance, when returning results with many relations (even if they are not joined and selected!).

When asking for the raw result set, the performance is just as good as it should be.