Re: Performance of query

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(dot)simic(at)gmail(dot)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 <cindymakarowsky(at)gmail(dot)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 <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
>>>
>>
>>
>