Re: querying with index on jsonb slower than standard column. Why?

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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