I changed the name of the table for the post but forgot to change it in the results of the explain. Table1 is busbase.
On Fri, Mar 22, 2013 at 6:25 PM, Misa Simic <misa.si...@gmail.com> wrote: > Hi, > > there is something mixed.. > > your index is on table1.... > > Explain Analyze reports about table called: busbase.... > > Kind Regards, > > Misa > > > > > 2013/3/22 Cindy Makarowsky <cindymakarow...@gmail.com> > >> 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 <j...@agliodbs.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@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-performance >>> >> >> >
