Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT

On 12/6/10 10:03 AM, Tom Lane wrote:
> John Papandriopoulos<dr.jpap@gmail.com>  writes:
>> I am still wondering whether the inheritance_planner(...) can be avoided if the rowtypes of children are the same as
theparent? 
>
> Possibly, but it's far from a trivial change.  The difficulty is that
> you'd need to generate a different plan tree structure.
> inheritance_planner generates a separate subtree for each target table,
> so that the ModifyTable node can execute each one separately and know
> a priori which target table the rows coming out of a particular subplan
> apply to.  If we expand inheritance "at the bottom" like SELECT does,
> that table identifier would have to propagate up as part of the returned
> rows.  It's doable but not simple.  Moreover, it's far from clear this
> actually would save much, and it could easily slow things down at
> execution time.

Making more sense now... :-)

I guess the real time-saver, in the specific case of partitioning, might then come from avoiding generation of subplans
completely(rather than later dropping the dummies) by exploiting the disjointness of each partition. 

> Have you done any profiling work to see where the extra time goes?
> I had thought that the unreferenced RTE entries would simply be ignored
> in each subplanning step, but maybe there's something that is examining
> them.

I've run the following queries

   explain SELECT * FROM ptest where id = 121212;
   explain DELETE FROM ptest where id = 121212;

under the Google perftools sampling profiler with the same 4096 child inheritance tree.  Results below.


The DELETE query-planning spend a lot of time maintaining a query tree.  Might this be what you're referring to?

> Total: 11808 samples
>     1895  16.0%  16.0%     7316  62.0% _range_table_mutator
>     1426  12.1%  28.1%     1426  12.1% _lseek
>     1097   9.3%  37.4%     2854  24.2% _query_planner
>     1048   8.9%  46.3%     1577  13.4% _AllocSetAlloc
>      853   7.2%  53.5%      853   7.2% 0x00007fffffe008a5
>      762   6.5%  60.0%      762   6.5% _posix_madvise
>      696   5.9%  65.9%      696   5.9% _list_nth_cell
>      575   4.9%  70.7%      575   4.9% 0x00007fffffe00b8b
>      482   4.1%  74.8%      482   4.1% _AllocSetFreeIndex
>      271   2.3%  77.1%     1284  10.9% _new_tail_cell
>      181   1.5%  78.6%      181   1.5% 0x00007fffffe00ba7
>      173   1.5%  80.1%      173   1.5% 0x00007fffffe00bb2
>      160   1.4%  81.5%     1452  12.3% _lappend
>      159   1.3%  82.8%      159   1.3% 0x00007fffffe00b96
>      158   1.3%  84.1%      158   1.3% 0x00007fffffe00b9c
>      139   1.2%  85.3%      139   1.2% 0x00007fffffe007c1
>      136   1.2%  86.5%     1877  15.9% _MemoryContextAlloc
>      129   1.1%  87.6%      129   1.1% 0x00007fffffe00673
>      125   1.1%  88.6%      125   1.1% 0x00007fffffe008ab
>      118   1.0%  89.6%      118   1.0% 0x00007fffffe008a0
>      110   0.9%  90.6%     3055  25.9% ___inline_memcpy_chk
>      106   0.9%  91.5%      106   0.9% _strlen
>      105   0.9%  92.3%      105   0.9% 0x00007fffffe008b7
>       95   0.8%  93.1%       95   0.8% _get_tabstat_entry
>       85   0.7%  93.9%       93   0.8% _find_all_inheritors
>       75   0.6%  94.5%       75   0.6% 0x00007fffffe00b85
>       47   0.4%  94.9%       47   0.4% 0x00007fffffe008b1
>       46   0.4%  95.3%       46   0.4% 0x00007fffffe00695
>       42   0.4%  95.6%       42   0.4% ___memcpy_chk
>       30   0.3%  95.9%       30   0.3% _pqGetpwuid
>       29   0.2%  96.1%       29   0.2% 0x00007fffffe00b90
>       29   0.2%  96.4%       60   0.5% _set_base_rel_pathlists
>       28   0.2%  96.6%       28   0.2% 0x00007fffffe007bf
>       24   0.2%  96.8%       24   0.2% 0x00007fffffe007cb
>       23   0.2%  97.0%       23   0.2% 0x00007fffffe006ab
>       22   0.2%  97.2%       23   0.2% _generate_base_implied_equalities
>       20   0.2%  97.4%       20   0.2% _memcpy
>       14   0.1%  97.5%       14   0.1% 0x00007fffffe0080d
>       13   0.1%  97.6%       13   0.1% _open
>       12   0.1%  97.7%       12   0.1% 0x00007fffffe007f9
> [rest snipped]


The SELECT query-planning doesn't, where you can clearly see that a lot of time is spent amassing all children
(find_all_inheritors)that could be avoided with true partitioning support. 

> Total: 433 samples
>      111  25.6%  25.6%      111  25.6% _AllocSetAlloc
>       79  18.2%  43.9%      124  28.6% _find_all_inheritors
>       38   8.8%  52.7%       38   8.8% _lseek
>       24   5.5%  58.2%       24   5.5% _read
>       19   4.4%  62.6%       32   7.4% _new_list
>       17   3.9%  66.5%       18   4.2% _get_tabstat_entry
>       14   3.2%  69.7%       36   8.3% _MemoryContextAllocZeroAligned
>       11   2.5%  72.3%       28   6.5% _MemoryContextAllocZero
>       11   2.5%  74.8%       19   4.4% _systable_beginscan
>        8   1.8%  76.7%        8   1.8% 0x00007fffffe007c5
>        8   1.8%  78.5%        8   1.8% 0x00007fffffe00a2f
>        8   1.8%  80.4%       32   7.4% _hash_search_with_hash_value
>        7   1.6%  82.0%        7   1.6% _open
>        6   1.4%  83.4%        6   1.4% 0x00007fffffe008c8
> [rest snipped]

Kindest,
John