Why is the selection by Primary Key very long?

Question

PostgreSQL

Good afternoon! The table is partitioned by months. In one of the tables about 1m records. Why does the fetch take about 150ms if you make a query
//150ms (April table) //301ms (parent table, for example) What can be done to speed up fetching from a partitioned table? In the postgres settings, there is logging of long requests exceeding 100ms (what is the optimal value?). the server is loaded.
SELECT * FROM table_2016_04 WHERE "id"=110033;
SELECT * FROM table WHERE "id"=110033;

EXPLAIN ANALYZE SELECT * FROM table_2016_04 WHERE "id"=110033;

Seq Scan on table_2016_04 (cost=0.00..65752.74 rows=1 width=3122) (actual time=121.602..167.554 rows=1 loops=1)
Filter: (id = 110033)
Rows Removed by Filter: 732260
Planning time: 0.652 ms
Execution time: 167.631 ms

Thank you! I really hope to help!

Answer 1

In order for PostgreSQL not to go through all partitions when fetching, you need to:
1. Create a CHECK constraint on each partition. Example:

ALTER TABLE table_2016_04 ADD CONSTRAINT c_table_2016_04
   CHECK ( my_date >= '20160401'::date  and  my_date <= '20160430'::date );

2. Specify a condition in the selection request that allows you to select the necessary partitions according to the condition specified in the CHECK constraint:
3. Perhaps the index on table_2016_04 by the id field will also speed up the selection.