Re: Performance of query

But, I do have an index on Table1 on the state field which is in my group
by condition:

CREATE INDEX statidx2
ON table1
USING btree
(state COLLATE pg_catalog."default" );

I have vacuumed the table too.
On Fri, Mar 22, 2013 at 5:13 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> On 03/22/2013 12:46 PM, Cindy Makarowsky wrote:
> > I've tried playing around with the settings in the config file for
> > shared_buffers, work_mem, etc restarting Postgres each time and nothing
> > seems to help.
>
> Well, you're summarizing 55 million rows on an unindexed table:
>
> " -> Seq Scan on busbase (cost=0.00..6378172.28 rows=55402728
> width=7) (actual time=0.004..250046.673 rows=60057057 loops=1)"
>
> ... that's where your time is going.
>
> My only suggestion would be to create a composite index which matches
> the group by condition on table1, and vacuum freeze the whole table so
> that you can use index-only scan on 9.2.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>