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
  • 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 '17 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. – YitzikC Sep 20 '17 at 13:17
  • I've tried vacuuming pg_class, pg_constraint, pg_inherits, pg_attribute and pg_type, and so far no effect... – YitzikC Sep 20 '17 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? – dezso Sep 20 '17 at 16:22
  • takes about 12 seconds, and sometimes a lot more the first time. Is it (much) faster after the first time? – Erwin Brandstetter Sep 20 '17 at 18:41
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?

  • 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! – YitzikC Sep 24 '17 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. – YitzikC Oct 31 '17 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. – YitzikC Nov 5 '17 at 11:30

Your Answer

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

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