Message-ID: <5485C8BA.7040704@aklaver.com>
Date: Mon, 08 Dec 2014 07:50:18 -0800
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:31.0) Gecko/20100101 Thunderbird/31.3.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Tim Dudgeon <tdudgeon.ml@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: querying with index on jsonb slower than standard column.
 Why?
References: <5484DBDA.6090405@gmail.com> <5484EEA7.1030403@aklaver.com>
 <5484F437.2080402@gmail.com> <16147.1418002090@sss.pgh.pa.us>
 <5485C449.4020204@aklaver.com> <26002.1418053572@sss.pgh.pa.us>
In-Reply-To: <26002.1418053572@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 12/08/2014 07:46 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 12/07/2014 05:28 PM, Tom Lane wrote:
>>> I don't see any particular difference ...
>
>> Running the above on my machine I do see the slow down the OP reports. I
>> ran it several times and it stayed around 3.5x.
>
> Interesting.  A couple of points that might be worth checking:
>
> * I tried this on a 64-bit build, whereas you were evidently using 32-bit.

My laptop is 64-bit, so when I get a chance I will setup the test there 
and run it to see what happens.

>
> * The EXPLAIN ANALYZE output shows that my bitmaps didn't go lossy,
> whereas yours did.  This is likely because I had cranked up work_mem to
> make the index builds go faster.
>
> It's not apparent to me why either of those things would have an effect
> like this, but *something* weird is happening here.
>
> (Thinks for a bit...)  A possible theory, seeing that the majority of the
> blocks are lossy in your runs, is that the reduction to lossy form is
> making worse choices about which blocks to make lossy in one case than in
> the other.  I don't remember exactly how those decisions are made.
>
> Another thing that seems odd about your printout is the discrepancy
> in planning time ... the two cases have just about the same planning
> time for me, but not for you.
>
> 			regards, tom lane
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com


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