Re: plan time of MASSIVE partitioning ...
От | Boszormenyi Zoltan |
---|---|
Тема | Re: plan time of MASSIVE partitioning ... |
Дата | |
Msg-id | 4CCA7F1D.8010509@cybertec.at обсуждение исходный текст |
Ответ на | Re: plan time of MASSIVE partitioning ... (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: plan time of MASSIVE partitioning ...
(Leonardo Francalanci <m_lists@yahoo.it>)
Re: plan time of MASSIVE partitioning ... (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
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/
Вложения
В списке pgsql-hackers по дате отправления: