Re: index fragmentation on insert-only table with non-unique column

On Wed, May 25, 2016 at 11:00 AM, Justin Pryzby <pryzby(at)telsasoft(dot)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.