agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Tim Dudgeon <tdudgeon.ml@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: querying with index on jsonb slower than standard column. Why?
Date: Mon, 08 Dec 2014 10:46:12 -0500
Message-ID: <26002.1418053572@sss.pgh.pa.us> (raw)
In-Reply-To: <5485C449.4020204@aklaver.com>
References: <5484DBDA.6090405@gmail.com>
<5484EEA7.1030403@aklaver.com>
<5484F437.2080402@gmail.com>
<16147.1418002090@sss.pgh.pa.us>
<5485C449.4020204@aklaver.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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.
* 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (24+ messages) latest in thread
Message-ID: <26002.1418053572@sss.pgh.pa.us>
Permalink: ../26002.1418053572@sss.pgh.pa.us/
Also on: postgresql.org/message-id/26002.1418053572@sss.pgh.pa.us
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: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, adrian.klaver@aklaver.com, tdudgeon.ml@gmail.com
Subject: Re: querying with index on jsonb slower than standard column. Why?
In-Reply-To: <26002.1418053572@sss.pgh.pa.us>
* 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