Planning of Select on PostgreSQL 9.6 partitioned table very slow

Question

I have a rather massive table of financial data in Postgres 9.6. It is partitioned by trading day (DATE type) and security ID (BIGINT). Overall the table comprises about 9300 partitions, takeing up 1765 GB in total. I use the conventional method of partitioning using CHECK constraints. The partitions are unlogged tables which I load in-bulk via COPY.

When I perform a query which can be satisfied from a single partition, the planner applies constraint exclusion and determines correctly which table should be consulted. However this takes about 12 seconds, and sometimes a lot more the first time. Could you suggest ways to help the planner work faster?

Example EXPLAIN ANALYZE output:

trading_offline=# EXPLAIN ANALYZE--                        
select * from public."ticks"
where trading_day ='2016-06-20'::date and inst_id= 1535681 and is_trade = true
and qty<-200;
                                                                        QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Append  (cost=0.00..16751.50 rows=16 width=177) (actual time=299.068..415.080 rows=5 loops=1)
->  Seq Scan on ticks  (cost=0.00..0.00 rows=1 width=161) (actual time=0.008..0.008 rows=0 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
->  Bitmap Heap Scan on "ticks$20160620$1535681"  (cost=766.35..16751.50 rows=15 width=177) (actual time=299.059..415.068 rows=5 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
        Rows Removed by Filter: 41833
        Heap Blocks: exact=10002
        ->  Bitmap Index Scan on "ticks$20160620$1535681_is_trade_idx"  (cost=0.00..766.35 rows=41323 width=0) (actual time=45.212..45.212 rows=41838 loops=1)
            Index Cond: (is_trade = true)
Planning time: 12233.724 ms
Execution time: 415.411 ms

Answer 1

The normal advice is not to go above 100 or so partitions, and 9300 is way beyond that. But still, it shouldn't take 12 seconds. Creating a simple simulation of 9300 partitions in 9.6, each with one constraint and one index, it takes about 1.2 seconds to plan a query (or about 2.0 seconds to do it the first time in a given session) which hits the parent and exactly one child.

I've repeated the simulation with two separate check constraints, instead of one constraint which ANDs together the conditions, and with an additional index on all the tables, and it didn't meaningfully change the results. Planning it is not speedy, but still nowhere near 12 seconds.

Can you monitor the system (with top, for example) to see what is going on? Or better yet, use perf top to identify the bottleneck?