MIME-Version: 1.0
In-Reply-To: <20160525140034.GB21220@telsasoft.com>
References: <20160524173914.GA11880@telsasoft.com>
 <CAMkU=1wsSEgByzYQ5uazD1gSsJh9RRYME6ehgJsUp3XcMm2Djg@mail.gmail.com>
 <20160525140034.GB21220@telsasoft.com>
From: Claudio Freire <klaussfreire@gmail.com>
Date: Fri, 3 Jun 2016 18:26:33 -0300
Message-ID: 
 <CAGTBQpbqkQYRbnz-7MJEhCL9bRhaE3sX12LZh4YGh4m+xoUqYA@mail.gmail.com>
Subject: Re: index fragmentation on insert-only table with
 non-unique column
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Wed, May 25, 2016 at 11:00 AM, Justin Pryzby <pryzby@telsasoft.com> wrote:
>> > First, I found I was able to get 30-50min query results on full week's table by
>> > prefering a seq scan to an index scan.  The row estimates seemed fine, and the
>> > only condition is the timestamp, so the planner's use of index scan is as
>> > expected.
>>
>> Can you show us the query?  I would expect a bitmap scan of the index
>> (which would do what you want, but even more so), instead.
> See explain, also showing additional tables/views being joined.  It's NOT doing
> a bitmap scan though, and I'd be interested to find why; I'm sure that would've
> improved this query enough so it never would've been an issue.
> https://explain.depesz.com/s/s8KP
>
>  ->  Index Scan using cdrs_huawei_pgwrecord_2016_05_01_recordopeningtime_idx on cdrs_huawei_pgwrecord_2016_05_01  (cost=0.56..1601734.57 rows=8943848 width=349)
>        Index Cond: ((recordopeningtime >= '2016-05-07 00:00:00'::timestamp without time zone) AND (recordopeningtime < '2016-05-08 00:00:00'::timestamp without time zone))

Please show your guc settings ( see
https://wiki.postgresql.org/wiki/Server_Configuration )

A plan node like that, if it would result in I/O, with proper
configuration should have selected a bitmap index/heap scan. If it
didn't, it probably thinks it has more cache than it really does, and
that would mean the wrong setting was set in effective_cache_size.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance