Re: 27 second plan times

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> The SearchCatCache here is the one in get_attavgwidth called to estimate the
>> relation width.
>
> Hmm, that information is supposed to be cached ... could you provide the
> test case?

Cached meaning it's stored in the cache once it's looked up to avoid future
lookups? In which case it will still mean 200k lookups since there really are
that many attributes.

Or cached meaning we prime the cache in one batch operation? It may well be
avoiding i/o, but there are suspiciously large numbers of calls to
SearchCatCache itself.

I'll send the pg_dump to pgsql-patches. But it's much larger than really
necessary to reproduce the effect. The planning time was already through the
roof before I added data and extra constraints. The key seems to be the wide
tables though, planning 2,000 child tables is fast if they only have a couple
columns.

>> The first thing that comes to mind is that we're doing the
>> constraint_exclusion code *after* estimating the width of the relations we're
>> going to exclude. If we push the constraint exclusion up a few lines the
>> planning time goes down the 1.7s.
>
> ... and probably breaks a few things; are you sure there are no needed
> side effects of the skipped code?

Well that's why I asked whether there was a reason we had to do it this way. I
haven't checked yet what those other pieces of code are doing exactly. But the
constraint exclusion doesn't depend on much else, I can't see anything related
to stats or indexes affecting it. The regression tests all pass but that
doesn't prove anything.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com