Re: [HACKERS] Optimizer fails?

David Gould wrote:

>  - 100kBps(sequentially) is way too slow. If you have time, try profileing
>    (with gprof) this scan. We should be able to do much better than this.
>    If you can't do it, we might want to put "Improve sequential scan rate"
>    on the todo list.

OK. I'll profile it as soon as I find some spare moment.

> > Of course, if I ask it without the index it takes ~3 minutes. That's why
> > expected that postgres would make some use of index. (The table is in
> > both cases the same).
>
> Just out of curiosity, how long do these queries take in MySQL vs postgreSQL?

MySQL seems to be aproximately 20 times better. Using a sequential scan
I can obserwe on IO monitor that it's able to read 2-5MB/s. (This is
two-disk RAID0 configuration, that has maximum throughput of 10MBps).

Of course,  you have to remember that mySQL has much simpler storage due
to lack of some features like transactions. I've read some Monty's (the
author) thoughts on transactions. He says that introducing transactions
would lower the performance at least 4-5 times (even read performance).

Now, I've downloaded personal version of Solid that I'm also going to
compare. What I personally find very interesting in Solid is it's
optimistic locking. Actually it means no locking at all. Solid seems to
have non-overwritting feature much like postgres. But this
non-overwritting feature is limited to not-checkpointed-yet data in
buffers (logs). Also, it maintains an internal structure called 'Bonsai
Tree', that includes all transaction's with their time. If there is a
collision between data it is deducted from this bonsai tree structure,
and then the latest transaction is rolled back.

By using systematic checkpointing it makes sure that those structures
are relatively small and conflicts are resolved quickly.

Of course, don't treat it as comparisions between postgres and
commercial product. I just want to share it as a kind of 'food for
thoughts'.

Mike

--
WWW: http://www.lodz.pdi.net/~mimo  tel: Int. Acc. Code + 48 42 148340
add: Michal Mosiewicz  *  Bugaj 66 m.54 *  95-200 Pabianice  *  POLAND