Re: Optimizer internals

On 6/15/06, Mark Lewis <mark(dot)lewis(at)mir3(dot)com> wrote:
>
>
> Unfortunately SUM is in the same boat as COUNT; in order for it to
> return a meaningful result it must inspect visibility information for
> all of the rows.
>
> -- Mark
>

We'll this is interesting news to say the least. We went with PostgreSQL for
our warehouse because we needed the advanced features that MySQL didn't have
at the time (views/sprocs).

It sounds like we almost need another fact table for the places that we do
SUM (which is not a problem just an additional map. If I'm interpreting this
all correctly, we can't force PG to bypass a sequence scan even if we know
our data is stable because of the MVCC aspect. In our case, as with most
warehouses (except those that do rolling loads during the day), we only
write data to it for about 5 hours at night in batch.

Any suggestions? FYI the original question wasn't meant as a poke at
comparing PG to MySQL to DB2. I'm not making an yvalue judgements either
way. I'm just trying to understand how we can use it the best way possible.

If anyone from the bizgres team is watching, have they done any work in this
area?

Thanks.
John