pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Sven R. Kunze <srkunze@mail.de>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Speeding up JSON + TSQUERY + GIN
Date: Mon, 6 Mar 2017 22:10:46 +0100
Message-ID: <4684e6c6-20db-769d-c0e6-76113bd2cef5@mail.de> (raw)
In-Reply-To: <CAMkU=1wvV2pgCJtSfj5HOQuK4255G5S2XGftw-D3b3Vx6YBoNw@mail.gmail.com>
References: <2260e4e6-89ae-90dc-278c-81243b3c3510@mail.de>
	<CAMkU=1xyf5ypGew=cgFuczQHqtc59J1CTf2EugF0hwSWu65ZCA@mail.gmail.com>
	<cffa8be6-4e43-7399-2f7a-5686f65acda2@mail.de>
	<CAMkU=1yWkWfZEZ=ANm-bWrDHBm869MCyRmyJDx5kbeCwJC_w2Q@mail.gmail.com>
	<b10df25c-c796-2cf0-60c0-040c31bc99a9@mail.de>
	<CAMkU=1zYmkgduM75whFbX9mE07juHj+Bkbovhja1wMzFJUpn4A@mail.gmail.com>
	<b2620eb5-c93d-fc95-7a94-65aa88d20866@mail.de>
	<CAMkU=1wvV2pgCJtSfj5HOQuK4255G5S2XGftw-D3b3Vx6YBoNw@mail.gmail.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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

view thread (11+ messages)

Message-ID: <4684e6c6-20db-769d-c0e6-76113bd2cef5@mail.de>
Permalink:  ../4684e6c6-20db-769d-c0e6-76113bd2cef5@mail.de/
Also on:    postgresql.org/message-id/4684e6c6-20db-769d-c0e6-76113bd2cef5@mail.de

 · 

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-performance@postgresql.org
  Cc: srkunze@mail.de, jeff.janes@gmail.com
  Subject: Re: Speeding up JSON + TSQUERY + GIN
  In-Reply-To: <4684e6c6-20db-769d-c0e6-76113bd2cef5@mail.de>

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

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