Re: plan time of MASSIVE partitioning ...

Tom Lane írta:
> Boszormenyi Zoltan <zb(at)cybertec(dot)at> writes:
>
>> Tom Lane írta:
>>
>>> AFAIR, canonical pathkeys are the *only* thing in the planner where pure
>>> pointer equality is interesting. So I doubt this hack is of any use for
>>> EquivalenceClass, even if the lists were likely to be long which they
>>> aren't.
>>>
>
>
>> No, for EquivalanceClass->ec_member, I need to do something
>> funnier, like implement compare(Node *, Node *) and use that
>> instead of equal(Node *, Node *)... Something like nodeToString()
>> on both Node * and strcmp() the resulting strings.
>>
>
> Well, (a) that doesn't work (hint: there are fields in nodes that are
> intentionally ignored by equal()),

Then this compare() needs to work like equal(), which can
ignore the fields that are ignored by equal(), too.
nodeToString would need more space anyway and comparing
non-equal Nodes can return the !0 result faster.

> and (b) I still don't believe that
> there's an actual bottleneck there. ECs generally aren't very big.
>

Actually, PlannerInfo->eq_classes needs to be a Tree somehow,
the comparator function is not yet final in my head.

equal() is called over 8 million times with or without our patch:

without

% cumulative self self total
time seconds seconds calls s/call s/call name
18.87 0.80 0.80 4812 0.00 0.00 make_canonical_pathkey
15.33 1.45 0.65 4811 0.00 0.00
get_eclass_for_sort_expr
14.15 2.05 0.60 8342410 0.00 0.00 equal
6.13 2.31 0.26 229172 0.00 0.00 SearchCatCache
3.66 2.47 0.16 5788835 0.00 0.00 _equalList
3.07 2.60 0.13 1450043 0.00 0.00
hash_search_with_hash_value
2.36 2.70 0.10 2272545 0.00 0.00 AllocSetAlloc
2.12 2.79 0.09 811460 0.00 0.00 hash_any
1.89 2.87 0.08 3014983 0.00 0.00 list_head
1.89 2.95 0.08 574526 0.00 0.00 _bt_compare
1.77 3.02 0.08 11577670 0.00 0.00 list_head
1.42 3.08 0.06 1136 0.00 0.00 tzload
0.94 3.12 0.04 2992373 0.00 0.00 AllocSetFreeIndex
0.94 3.16 0.04 91427 0.00 0.00 _bt_checkkeys
...

with

% cumulative self self total
time seconds seconds calls s/call s/call name
24.51 0.88 0.88 4811 0.00 0.00
get_eclass_for_sort_expr
14.21 1.39 0.51 8342410 0.00 0.00 equal
8.22 1.69 0.30 5788835 0.00 0.00 _equalList
5.29 1.88 0.19 229172 0.00 0.00 SearchCatCache
2.51 1.97 0.09 1136 0.00 0.00 tzload
2.23 2.05 0.08 3014983 0.00 0.00 list_head
2.23 2.13 0.08 2283130 0.00 0.00 AllocSetAlloc
2.09 2.20 0.08 811547 0.00 0.00 hash_any
2.09 2.28 0.08 11577670 0.00 0.00 list_head
1.95 2.35 0.07 1450180 0.00 0.00
hash_search_with_hash_value
1.39 2.40 0.05 640690 0.00 0.00 _bt_compare
1.39 2.45 0.05 157944 0.00 0.00 LockAcquireExtended
1.39 2.50 0.05 11164 0.00 0.00 AllocSetCheck
1.11 2.54 0.04 3010547 0.00 0.00 AllocSetFreeIndex
1.11 2.58 0.04 874975 0.00 0.00 AllocSetFree
1.11 2.62 0.04 66211 0.00 0.00 heap_form_tuple
0.84 2.65 0.03 888128 0.00 0.00 LWLockRelease
...

The number of calls are the same for equal and _equalList
in both cases as you can see.

And if you compare the number of AllocSetAlloc calls,
it's actually smaller with our patch, so it's not that the
conversion to Tree caused this.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/