Re: Performance of query

[Cindy Makarowsky <cindymakarowsky@xxxxxxxxx>]

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.simic@xxxxxxxxx> 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 <cindymakarowsky@xxxxxxxxx>

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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance