From: Jordi <jmaillists@promani.be>
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: Re: Bitmap and-ing between btree and gin?
Date: Thu, 4 Feb 2016 18:19:31 +0100
Message-ID: <56B38823.40809@promani.be> (raw)
In-Reply-To: <17855.1454602121@sss.pgh.pa.us>
References: <56B332B6.1040109@promani.be>
<17855.1454602121@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi Tom, thanks for your reply, much appreciated.
So basically you're saying it's hard to do sorting in any way when a gin
index is involved? Neither with a complete multi-column btree_gin index
because it doesn't support sorting per definition, nor with a seperate
gin and btree because there would be an extra post-sorting step involved
over the FULL resultset (because of the LIMIT).
Then would you have any hint on how to implement pagination when doing
full text search?
Cause in theory, if I gave it a id>100 LIMIT 100, it might just as well
return me results 150 to 250, instead of 100 to 200...
PS: I already tried maxing the statistics target setting and running
ANALYSE after, with no change.
Regards,
Jordi
On 04-02-16 17:08, Tom Lane wrote:
> Jordi <jmaillists@promani.be> writes:
>> I've been trying to get a query use indexes and it has raised a doubt
>> whether pgsql supports bitmap and-ing between a multi-column btree index
>> and a gin index.
> Sure. But such a plan would give an unordered result, meaning that we'd
> have to process the whole table before doing the ORDER BY/LIMIT. The
> planner evidently thinks that it's better to try to process the rows in
> ID order so it can stop as soon as it's got 100. If it's wrong about
> that, that's likely because it's got a bad estimate of the selectivity of
> the other WHERE conditions. You might see if you can improve the
> statistics for the search_vector column.
>
> regards, tom lane
>
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: jmaillists@promani.be, tgl@sss.pgh.pa.us
Subject: Re: Bitmap and-ing between btree and gin?
In-Reply-To: <56B38823.40809@promani.be>
* 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