Why is fetching slow in Postgresql partitioned tables?

Question

PostgreSQL

Good day!
I am using Postgresql 9.5. Out of the box settings.
Partitioned the table into ~ 7000 partitions. Inserted one entry.
When I do , the request lasts 1.5 seconds. When I do , where bid is a partitioning constraint (one table per bid value), the query lasts 1 second. Here is EXPLAIN ANALYZE for the second option:SELECT * FROM "Offer";
SELECT * FROM "Offer" WHERE bid=4793;

Append  (cost=0.00..12.14 rows=2 width=596) (actual time=0.014..0.014 rows=1 loops=1)
  ->  Seq Scan on "Offer"  (cost=0.00..1.01 rows=1 width=344) (actual time=0.011..0.011 rows=0 loops=1)
        Filter: (bid = 4793)
        Rows Removed by Filter: 1
  ->  Seq Scan on "Offer-4793"  (cost=0.00..11.12 rows=1 width=848) (actual time=0.002..0.002 rows=1 loops=1)
        Filter: (bid = 4793)
Planning time: 996.243 ms
Execution time: 0.261 ms

Why so long? How can you profile this?
My only guess is that postgresql doesn't keep the constraint conditions of its partitions in RAM and reads from disk each time.
I really look forward to helping!
UPDATE:
I was also advised cascading partitioning (the main table has 10 children, each of which also has ten children, etc.). The result is no better:

Append  (cost=0.00..12.24 rows=5 width=848) (actual time=0.013..0.013 rows=1 loops=1)
  ->  Seq Scan on "Offer"  (cost=0.00..1.11 rows=1 width=848) (actual time=0.006..0.006 rows=0 loops=1)
    Filter: (bid = 4793)
  ->  Seq Scan on "Offer-ddd-3"  (cost=0.00..0.00 rows=1 width=848) (actual time=0.001..0.001 rows=0 loops=1)
    Filter: (bid = 4793)
  ->  Seq Scan on "Offer-dd-33"  (cost=0.00..0.00 rows=1 width=848) (actual time=0.000..0.000 rows=0 loops=1)
    Filter: (bid = 4793)
  ->  Seq Scan on "Offer-d-336"  (cost=0.00..0.00 rows=1 width=848) (actual time=0.000..0.000 rows=0 loops=1)
    Filter: (bid = 4793)
  ->  Seq Scan on "Offer-4793"  (cost=0.00..11.12 rows=1 width=848) (actual time=0.006..0.006 rows=1 loops=1)
    Filter: (bid = 4793)
Planning time: 1449.872 ms
Execution time: 0.354 ms

Answer 1

and you have indexes on these fields in child tables?
https://www.postgrespro.ru/doc/ddl-inherit.html#DD...

Inheritance is severely limited by the fact that indexes (including unique constraints) and foreign key constraints only apply to individual tables, not their descendants. This goes for both sides of foreign key constraints. So, for our example:

Answer 2

Show explain analyze. Judging by the cost in explain, the query execution itself is correct.
The working version - the scheduler eats time. 7k partitions is a lot. There is even a note about this in the manual (about which there are just no notes, but try to notice them)
https://www.postgresql.org/docs/9.4/static/ddl-par...
In postgresql, partitioning is done rather strangely and, generally speaking, by means not intended for this. If you have a large enough table to feel the benefits of partitioning, then it is better to limit yourself to a couple of dozen sections. Here is another thematic article: https://habrahabr.ru/post/273933/

Answer 3

https://www.postgresql.org/docs/9.5/static/ddl-par...

All constraints on all partitions of the master table are examined during constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably. Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions.