Skip site navigation (1) Skip section navigation (2)

Re: plan time of MASSIVE partitioning ...

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Leonardo Francalanci <m_lists(at)yahoo(dot)it>
Cc: Boszormenyi Zoltan <zb(at)cybertec(dot)at>, pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: plan time of MASSIVE partitioning ...
Date: 2010-10-29 17:15:55
Message-ID: 3943.1288372555@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
I wrote:
> This is going to be dominated by constraint exclusion checking.

Hmm, maybe I spoke too soon.  With 9000 child tables I get a profile
like this:

samples  %        symbol name
447433   47.1553  get_tabstat_entry
185458   19.5456  find_all_inheritors
53064     5.5925  SearchCatCache
33864     3.5690  pg_strtok
26301     2.7719  hash_search_with_hash_value
22577     2.3794  AllocSetAlloc
6696      0.7057  MemoryContextAllocZeroAligned
6250      0.6587  expression_tree_walker
5141      0.5418  LockReleaseAll
4779      0.5037  get_relation_info
4506      0.4749  MemoryContextAlloc
4467      0.4708  expression_tree_mutator
4136      0.4359  pgstat_initstats
3914      0.4125  relation_excluded_by_constraints

get_tabstat_entry and find_all_inheritors are both obviously O(N^2) in
the number of tables they have to deal with.  However, the constant
factors are small enough that you need a heck of a lot of tables
before they become significant consumers of runtime.  I'm not convinced
that we should be optimizing for 9000-child-table cases.

It'd be worth fixing these if we can do it without either introducing a
lot of complexity, or slowing things down for typical cases that have
only a few tables.  Offhand not sure about how to do either.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-10-29 17:16:44
Subject: Re: plan time of MASSIVE partitioning ...
Previous:From: Tom LaneDate: 2010-10-29 16:53:30
Subject: Re: plan time of MASSIVE partitioning ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group