On Fri, Jun 19, 2015 at 9:20 AM, Robert Haas <robertmh...@gmail.com> wrote: > The extraordinarily planning time for query 4 is caused by a > completely different problem: SearchCatCache eats up huge amounts of > CPU; its callers are get_attavgwidth and get_typlen. It's not clear > to me why doubling the number of relations causes such an enormous > explosion in calls to those functions; I would expect the number of > calls to double, but presumably the actual increase is much more. > That's a separate problem, though, unconnected to > c03ad5602f529787968fa3201b35c119bbc6d782 and not necessarily a > regression.
I don't have a great high level understanding of the planner, and Q4 may be somehow asking for trouble or unrepresentative of anything useful, but I did some profiling and instrumenting, and I noticed that we spend tables^2 * columns time in get_attavgwidth. I wonder if estimate_rel_size (or some other function in that stack, or some new function wrapper) should remember the result for each relation for the scope of this planner invocation. That should bring the calls to get_attavgwidth down to the same order as Q3 (tables * columns). Here is some profiler output from a 500 table, 500 column Q4 run: 160295.0ms 60.2% 95 inheritance_planner 120064.0ms 45.1% 0 grouping_planner 119826.0ms 45.0% 2 query_planner 114204.0ms 42.9% 0 add_base_rels_to_query 114204.0ms 42.9% 0 add_base_rels_to_query 114204.0ms 42.9% 151 build_simple_rel 113817.0ms 42.8% 57 build_simple_rel 113600.0ms 42.7% 19 get_relation_info 112123.0ms 42.1% 27 estimate_rel_size 111557.0ms 41.9% 14139 get_rel_data_width 80152.0ms 30.1% 362 get_attavgwidth 79788.0ms 30.0% 282 SearchSysCache 79368.0ms 29.8% 52373 SearchCatCache 13182.0ms 4.9% 2125 CatalogCacheComputeHashValue Here are some tables showing function call counts. The columns are ordered like this: 1: Query number 2: Number of child tables 3: Number of columns 4: Number of calls to add_base_rels_to_query 5: Number of calls to build_simple_rel 6: Number of calls to get_relation_info 7: Number of calls to estimate_rel_size 8: Number of calls to get_attavgwidth Q3 10 10 22 11 11 11 131 Q3 10 20 22 11 11 11 241 Q3 10 30 22 11 11 11 351 Q3 20 10 42 21 21 21 251 Q3 20 20 42 21 21 21 461 Q3 20 30 42 21 21 21 671 Q3 30 10 62 31 31 31 371 Q3 30 20 62 31 31 31 681 Q3 30 30 62 31 31 31 991 Q3 500 500 1002 501 501 501 251501 Q4 10 10 33 143 143 132 1451 Q4 10 20 33 143 143 132 2661 Q4 10 30 33 143 143 132 3871 Q4 20 10 63 483 483 462 5291 Q4 20 20 63 483 483 462 9701 Q4 20 30 63 483 483 462 14111 Q4 30 10 93 1023 1023 992 11531 Q4 30 20 93 1023 1023 992 21141 Q4 30 30 93 1023 1023 992 30751 Q4 500 500 1503 252003 252003 251502 126002501 -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
