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
