Re: plan time of MASSIVE partitioning ...

Tom Lane írta:
> I wrote:
>
>> the right way to make this faster is to refactor things so that we
>> don't generate useless equivalence classes in the first place, or
>> at least don't keep them around in the planner's lists once we realize
>> they're useless.
>>
>
> After a bit of hacking, I propose the attached patch.
>
>
>> I like Heikki's hack to cut down on searching in make_canonical_pathkey,
>> but I think that complicating the data structure searching beyond that
>> is just a band-aid.
>>
>
> With the given test case and this patch, we end up with exactly two
> canonical pathkeys referencing a single EquivalenceClass. So as far
> as I can tell there's not a lot of point in refining the pathkey
> searching. Now, the EquivalenceClass has got 483 members, which
> means that there's still some O(N^2) behavior in
> get_eclass_for_sort_expr. There might be some use in refining the
> search for a matching eclass member. It's not sticking out in
> profiling like it did before though.
>
> regards, tom lane
>

Thanks, this patch made get_eclass_from_sort_expr almost,
make_canonical_pathkeys and add_child_rel_equivalences
completely disappear from the gprof timing.

+1 for including this into 9.1.

On the other hand, if I use a similar test case to my original one
(i.e. the tables are much wider) then the query planning takes
1.42 seconds in 9.1 with this patch instead of about 4.7 seconds
as we observed it using PostgreSQL 9.0.0. The beginning of the gprof
output now looks like this:

Each sample counts as 0.01 seconds.
% cumulative self self total
time seconds seconds calls s/call s/call name
21.13 0.30 0.30 235091 0.00 0.00 SearchCatCache
7.04 0.40 0.10 1507206 0.00 0.00
hash_search_with_hash_value
3.52 0.45 0.05 2308219 0.00 0.00 AllocSetAlloc
3.52 0.50 0.05 845776 0.00 0.00 hash_any
3.52 0.55 0.05 341637 0.00 0.00 HeapTupleSatisfiesNow
3.52 0.60 0.05 1136 0.00 0.00 tzload
2.82 0.64 0.04 547 0.00 0.00 get_rel_data_width
2.11 0.67 0.03 669414 0.00 0.00 hash_search
2.11 0.70 0.03 235091 0.00 0.00 SearchSysCache
2.11 0.73 0.03 192590 0.00 0.00 copyObject
2.11 0.76 0.03 164457 0.00 0.00 pgstat_initstats
2.11 0.79 0.03 152999 0.00 0.00 index_getnext
...

Use the attached synthetic create_table_wide.sql together with the
previous childtables.sql. The full compressed gprof output is attached.
Your patch creates a 70% speedup in planning time, which is excellent.

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/