Re: Plan time Improvement - 64bit bitmapset

Hi,

On 06/03/2009 06:42 PM, Tom Lane wrote:
> Andres Freund<andres@anarazel.de>  writes:
>> On 06/03/2009 06:21 PM, Tom Lane wrote:
>>> I find this *really* hard to believe, because I've never seen the bitmap
>>> support operations show up noticeably at all in profiles.  What sort of
>>> queries are you testing?
>> Many left joins from one base relation to additional dimensions. All the
>> dimensions are relatively complex views consisting out of multiple joins
>> or subselects.
>> Some correlated subqueries and some [NOT] EXISTS() are also included in
>> some of the queries.
> Hmmm, could you provide a complete test case?  I'm thinking the behavior
> might indicate some other performance issue, ie an unreasonable number
> of bitmapset calls in some particular planning path.
Ok. I tried to reproduce it using only pg_catalog and suceeded to some
degree:
- The query is pointless, pointless, pointless
- The effect is only around 5-10% instead of the 15-20% I have measured
(fewer tables involved - fewer cache misses?)
- The query is crazy, but so is the one on the schema in question
- I could get more consistent results with geqo disabled, but the
results are in the same ballpark whether enabled or not
- Sometimes adding a single join more/less dropped the planning time to
a fraction - strange.
- The same with changing {join,from}_collapse_limit - sometimes changing
it yields plan times different by orders of magnitudes in both directions.

On the real data its naturally not only one view but multiple ones...
And there are fewer views involved, but they are more complex (including
EXISTS(), and some subqueries).

Plan time (averaged) without change:
cnt: 40 (4 times per session)
avg: 4572ms

Plan time (averaged) with change:
cnt: 40 (4 times per session)
avg: 4236ms

~7% difference. Same with higher number of repetitions and with most
other planner settings I tried

Now thats not a lot of change, but again, this is smaller than with the
original queries.

Does that help?

Andres