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(dot)jpap(at)gmail(dot)com> writes:
>> I am still wondering whether the inheritance_planner(...) can be avoided if the rowtypes of children are the same as the parent?
>
> 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