Re: dbt3 data with 10GB scale factor

On Tue, 2005-08-02 at 14:22 -0700, Mark Wong wrote:
> I've started scaling dbt3 up to the 10GB scale factor against CVS and
> the fast COPY patch:
>
> http://www.testing.osdl.org/projects/dbt3testing/results/dev4-010/53/

Try "www1" if this URL fails for you.

> I'm sure there are some better database parameters I should use so
> please let me know what to try. ;) What I've found interesting is the
> difference in the time it takes for Q9 to run in the power test than the
> throughput test:
>
> Power Test 00:56:27
> Throughput Stream 1 00:38:13
> Throughput Stream 2 00:41:33
> Throughput Stream 3 00:20:16
> Throughput Stream 4 00:18:11
>
> Diffing the query plans between the Power Test and the individual
> streams in the Throughput Test, I only see a Materialize and Seq Scan on
> the nation table versus just a Seq Scan on the nation table between two
> of the plans. But this doesn't appear to account for the execution time
> difference as the query with the same plan executes just as fast during
> the Throughput Test. Here are the plans in full:

Very interesting. Excellent work on the test results.

Initial reaction was shock, but: most of these plans use nested joins,
so there's lots of shared_buffers work going on. It feels like this
would allow the queries to share a certain percentage of blocks and
allow the multiple backends to parallelise the I/O, which in the Power
test would all be single streamed.

Difference in plans? Well, they are actually all different queries, just
very similar. I note that the number of rows retrieved by the Power Test
query actually had the fewest number of rows of any query, so the time
difference might well have been much greater.

I'll look more into the plan differences.

Best Regards, Simon Riggs