Skip site navigation (1) Skip section navigation (2)

Re: Optimizer internals

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: John Vincent <lusis(dot)org(at)gmail(dot)com>
Cc: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>, PGSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Optimizer internals
Date: 2006-06-24 04:32:27
Message-ID: 20060624043227.GA13888@wolff.to (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Jun 15, 2006 at 15:38:32 -0400,
  John Vincent <lusis(dot)org(at)gmail(dot)com> wrote:
> 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.
> >
> 
> Actually we just thought about something. With PG, we can create an index
> that is a SUM of the column where indexing, no? We're going to test this in
> a few hours. Would that be able to be satisfied by an index scan?

No, that won't work. While you can make indexes on functions of a row, you
can't make indexes on aggregate functions.

You might find making a materialized view of the information you want can
help with performance. The issues with "sum" are pretty much the same ones
as with "count". You can find a couple different ways of doing materialized
views for "count" in the archives. There is a simple way of doing it that
doesn't work well with lots of concurrent updates and a more complicated
method that does work well with lots of concurrent updates.

In response to

pgsql-performance by date

Next:From: Craig A. JamesDate: 2006-06-25 18:15:02
Subject: Sort order in sub-select
Previous:From: Franklin HautDate: 2006-06-24 00:27:40
Subject: RES: Temporary table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group