2

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
5
  • It's quite possible your pg_class et al tables are at the size where scanning them for planning is slow. Have you tried vacuuming the system catalog tables a few times to remove any potential bloat? Another (less desirable) option is to query the partitions directly, and by extension, you could create a sql/plpgsql function that supplies the partition names for the queries. That was a hack I had to resort to back in 8.4-era with multi-TB partitioned tables.
    – bma
    Sep 20, 2017 at 13:16
  • Indeed querying the partitions directly make the planning essentially instant. However I'd rather not expose my end-users to the partitions directly. Sep 20, 2017 at 13:17
  • I've tried vacuuming pg_class, pg_constraint, pg_inherits, pg_attribute and pg_type, and so far no effect... Sep 20, 2017 at 13:38
  • Well, having this many partitions will invariably lead to slow planning, in my experience. How big is the table and what is the partitioning scheme? Sep 20, 2017 at 16:22
  • takes about 12 seconds, and sometimes a lot more the first time. Is it (much) faster after the first time? Sep 20, 2017 at 18:41

1 Answer 1

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?

3
  • As I mentioned my table is partitioned along 2 orthogonal axes - business day and security ID, and I use to distinct CHECKs. Would you expect a single compound condition to work better? The examples here seem to suggest a single CHECK is preferred. Also, thanks for suggesting perf top! Sep 24, 2017 at 9:06
  • I'm trying to partition the table with 2 levels of inheritance, so that 1st level of of inheriting tables constitute per business-day partitions, and they are further partitioned by security ID. The perminary results seem promising. If this resolves the issue I'll post an answer. Oct 31, 2017 at 17:57
  • Experimenting on a larger scale with the 2-level partitioning scheme I've conceived gave the same poor performance as before, so I'm still without an adequate solution. Nov 5, 2017 at 11:30

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.