1

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
6
  • 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.
    – ILCAI
    Feb 24, 2023 at 13:16
  • 1
    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
    Feb 24, 2023 at 13:19
  • 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.
    – user1822
    Feb 24, 2023 at 13:36
  • ... 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.
    – ILCAI
    Feb 24, 2023 at 13:41
  • 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.
    – user1822
    Feb 24, 2023 at 15:17

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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