I have a table with more than 40 million rows and I want to know what is the best way I can optimize it for searches. The search will happen by the person's document number, and these document numbers are composed by nine numbers from 100000000 to 999999999.

My solution was to split this table into other tables identified by the two first numbers of document that those tables will store, something like, table_10 (documents from 100000000 to 109999999), table_11 (documents from 110000000 to 119999999), table_12, table_13, ..., table99. And when the user searches for the document 10... I will build the SQL to search in table_10.

I'm just a mortal programmer not experienced with large databases, and I don't know if my solution is good, but this is the only solution to make the system faster that I have found. Do you have other recommendations?

  • 2
    40 Million is not really that large. With proper indexing you probably don't need to partition your table. But if your query is slow, please read this: wiki.postgresql.org/wiki/Slow_Query_Questions and then post the missing information. – a_horse_with_no_name Apr 27 '15 at 20:05
  • At minimum you need to post some table definitions, database version, and explain (buffers, verbose, analyze) output. – Craig Ringer Apr 28 '15 at 1:21
  • The columns were not indexed, so I have made my searches much more faster by indexing them. Now I'm going to see other configuration tricks to help me to get faster and faster. With indexes my aplication unit test had a amazing downfall, from 65 to 32 seconds. Thank you guys. – hamboldt Apr 28 '15 at 14:36
  • Also, what you're doing manually is called horizontal partitioning. It's implemented in PostgreSQL through the table inheritance system. A good start might be to look at pg_partman. – Clément Prévost May 30 '15 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.