public inbox for [email protected]  

help / color / mirror / Atom feed

From: Claudio Freire <[email protected]>
To: Justin Pryzby <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: index fragmentation on insert-only table with non-unique column
Date: Fri, 3 Jun 2016 18:26:33 -0300
Message-ID: <CAGTBQpbqkQYRbnz-7MJEhCL9bRhaE3sX12LZh4YGh4m+xoUqYA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CAMkU=1wsSEgByzYQ5uazD1gSsJh9RRYME6ehgJsUp3XcMm2Djg@mail.gmail.com>
	<[email protected]>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

On Wed, May 25, 2016 at 11:00 AM, Justin Pryzby <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: index fragmentation on insert-only table with non-unique column
  In-Reply-To: <CAGTBQpbqkQYRbnz-7MJEhCL9bRhaE3sX12LZh4YGh4m+xoUqYA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox