0

I have a table that has 44 columns and slightly less than 2000 rows. Also, the data are all text or integers. Texts don't go longer than a few characters. Table size is about 1900kb. All my other tables' planning time is less than 10ms.

explain analyze select * from my_table_1 where my_table_1_primary_key=1

query as simple as this yields planning time: 2000~6000ms execution time: 0.6~2ms

My suspect for the problem is

  1. pg_catalog.pg_statistic being 588MB for unknown reason. => the grand sum of all my tables is less than 10 MB of data.
  2. I do very frequent update for my_table_1. About 50 updates every 2 minutes, each update updating 5 columns of corresponding single rows.
  3. I have 6 unique columns which auto generates 7 indexes in total. (including single column primary key)

Any ideas on my situation?

Improve this question
2
  • If you are mostly interested in fixing the problem (rather than assigning blame for it), I would start by doing VACUUM FULL followed by VACUUM ANALYZE on both my_table_1 and pg_statistic and see if that takes care of it. Assuming you can put up with the lock time.
    – jjanes
    Aug 21 at 14:08
  • thnx for the advice. I tried myariads of solutions and it turns out vendor specific problem. Direct connection to DB had explain result to be 2ms planing time, but connection from a vendor dashboard was the issue.
    – Seho
    Aug 22 at 13:48

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.