On 12/6/10 10:03 AM, Tom Lane wrote: > John Papandriopoulos<dr.jpap@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
