FW: Tuning queries on large database

[forwarded to performance]
> The result is that for "short queries" (Q1 and Q2) it runs in a few
> seconds on both Oracle and PG. The difference becomes important with
> Q3 : 8 seconds with oracle
> 80 sec with PG
> and too much with Q4 : 28s with oracle
> 17m20s with PG !
>
> Of course when I run 100 or 1000 parallel queries such as Q3 or Q4,
> it becomes a disaster !
> I can't understand these results. The way to execute queries is the
> same I think. I've read recommended articles on the PG site.
> I tried with a table containing 30 millions rows, results are similar.

I don't trust the Oracle #s. Lets look at Q4: returns 3 million rows.
Using your #s of 160 fields and 256 bytes, your are asking for a result
set of 160 * 256 * 3M = 12 GB! This data has to be gathered by the
disk, assembled, and sent over the network.

I don't know Oracle, but it probably has some 'smart' result set that
uses a cursor behind the scenes to do the fetching.

With a 3M row result set, you need to strongly consider using cursors.
Try experimenting with the same query (Q4), declared as a cursor, and
fetch the data in 10k blocks in a loop (fetch 10000), and watch the #s
fly.

Merlin