Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ckzuA-0001OP-VA for pgsql-performance@arkaria.postgresql.org; Mon, 06 Mar 2017 21:10:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ckzuA-0006ZA-AV for pgsql-performance@arkaria.postgresql.org; Mon, 06 Mar 2017 21:10:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ckzu9-0006YJ-8X for pgsql-performance@postgresql.org; Mon, 06 Mar 2017 21:10:53 +0000 Received: from shout01.mail.de ([2001:868:100:600::216]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ckzu5-0004PK-Si for pgsql-performance@postgresql.org; Mon, 06 Mar 2017 21:10:51 +0000 Received: from postfix03.mail.de (postfix03.bt.mail.de [10.0.121.127]) by shout01.mail.de (Postfix) with ESMTP id 28ACC4138E; Mon, 6 Mar 2017 22:10:48 +0100 (CET) Received: from smtp03.mail.de (smtp03.bt.mail.de [10.0.121.213]) by postfix03.mail.de (Postfix) with ESMTP id 11A4240126; Mon, 6 Mar 2017 22:10:48 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=mail.de; s=mailde201610; t=1488834648; bh=Uu72koziesU2jTiRXmJWbmkA6PCnwTnIVukNF6zCb+I=; h=Subject:To:References:Cc:From:Date:In-Reply-To:From; b=eWGiyIcuVLpkeyEhZ2rEDfUCz4Kv/03RooFNOUDN42dXr1VqHlAz5/80nIwHlWrze GI9X1ksUR9cyJufPbh3+L0Z5FmvosYFko14Zqj2kGrzFFAhXRePY0+6RX2ShuSiDG8 +6GhT9aa+gNkFm6YvCevH6j9QFvmotus6K6OcrsI= Received: from [127.0.0.1] (localhost [127.0.0.1]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by smtp03.mail.de (Postfix) with ESMTPSA id A86FB40020; Mon, 6 Mar 2017 22:10:47 +0100 (CET) Subject: Re: Speeding up JSON + TSQUERY + GIN To: Jeff Janes References: <2260e4e6-89ae-90dc-278c-81243b3c3510@mail.de> Cc: "pgsql-performance@postgresql.org" From: "Sven R. Kunze" Message-ID: <4684e6c6-20db-769d-c0e6-76113bd2cef5@mail.de> Date: Mon, 6 Mar 2017 22:10:46 +0100 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------F696B02DACF958211B05CCE7" X-purgate: clean X-purgate: This mail is considered clean (visit http://www.eleven.de for further information) X-purgate-type: clean X-purgate-Ad: Categorized by eleven eXpurgate (R) http://www.eleven.de X-purgate: This mail is considered clean (visit http://www.eleven.de for further information) X-purgate: clean X-purgate-size: 3690 X-purgate-ID: 154282::1488834647-00000CF2-3069FD23/0/0 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------F696B02DACF958211B05CCE7 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 06.03.2017 05:25, Jeff Janes wrote: > Bitmaps can overflow and drop the row-level information, tracking only > the blocks which need to be inspected. So it has to have a recheck in > case that happens (although in your case it is not actually > overflowing--but it still needs to be prepared for that). Good to know. > Also, I think that jsonb_path_ops indexes the hashes of the paths, so > it can deliver false positives which need to be rechecked. Wow, that's a very important piece of information. It explains a lot. Thanks a lot. > And you are selecting `id`, which is not in the index so it would have > to consult the table anyway to retrieve that. Even if it could get > all the data from the index itself, I don't think GIN indexes support > that feature. Yes, I see. I actually was sloppy about the query. What's really important here would be counting the number of rows. However, from what I can see, it's the best PostgreSQL can do right now. Or you have any more ideas how to speed up counting? Best, Sven --------------F696B02DACF958211B05CCE7 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 06.03.2017 05:25, Jeff Janes wrote:
Bitmaps can overflow and drop the row-level information, tracking only the blocks which need to be inspected.  So it has to have a recheck in case that happens (although in your case it is not actually overflowing--but it still needs to be prepared for that).

Good to know.

Also, I think that jsonb_path_ops indexes the hashes of the paths, so it can deliver false positives which need to be rechecked.

Wow, that's a very important piece of information. It explains a lot. Thanks a lot.

And you are selecting `id`, which is not in the index so it would have to consult the table anyway to retrieve that.  Even if it could get all the data from the index itself, I don't think GIN indexes support that feature.

Yes, I see. I actually was sloppy about the query. What's really important here would be counting the number of rows. However, from what I can see, it's the best PostgreSQL can do right now.


Or you have any more ideas how to speed up counting?


Best,
Sven
--------------F696B02DACF958211B05CCE7--