Re: Plan time Improvement - 64bit bitmapset

On 06/03/2009 07:05 PM, Kevin Grittner wrote:
> Andres Freund<andres@anarazel.de>  wrote:
>
>> long plan times (minutes and up)
>
> Wow.  I thought I had some pretty complex queries, including some
> which join using several views, each of which has several joins; but
> I've never gone to multiple seconds on plan time (much less multiple
> minutes!) without very high statistics targets and many indexes on the
> tables.  Any rough estimates on those?
Statistics target is 250. Lowering to 10 lowers the query plan time 
somewhat but not significantly and increases query runtime significantly.

Real dataset is a bit less than 1.5TB without materialized views and a 
bit over 3 with.
Production machine (old) is a 2xDualcore Xeon 5150, 32gig ram.

Test Dataset is about 15GB. Core2 Duo 2.4Ghz, 4GB ram.

Example query (from which the traces are) on the test dataset (I cant 
simply do a full analyze on the real data):
Stat target 10: 22283.187ms PREPARE
Stat target 1000: 23986.504ms PREPARE

So, no really interesting difference.

For the timings I always PREPARE'ed the query multiple times in a 
transaction to make sure there are no caching effects - a small drop but 
nothing significant.


On the average its about
> If you think your patch could have a significant impact on a query
> with a 260 ms plan time, I could give it a try.From what I have seen so far I doubt that it will have a really 
measurable effect on relatively short planning times- if you want to try 
its a very simple change:

Just change all 32 into the 64 bit equivalents in include/nodes/bitmapset.h:
#define BITS_PER_BITMAPWORD 32
typedef uint32 bitmapword;            /* must be an unsigned type */
typedef int32 signedbitmapword; /* must be the matching signed type */


Andres