MIME-Version: 1.0
In-Reply-To: 
 <CAF4Au4ws4ackrp2_gNPEfiJ_O6FLKz4ncOjwym-X0wLL52SDKA@mail.gmail.com>
References: <27201.1458135447@sss.pgh.pa.us>
	<VisenaEmail.55.5cac9849d07151e7.1537fb2771f@tc7-visena>
	<CAMkU=1zAcFQAdysVY6fyyR0k5OTgWvoja2TpkGBhe5t0E9RopA@mail.gmail.com>
	<CAF4Au4ws4ackrp2_gNPEfiJ_O6FLKz4ncOjwym-X0wLL52SDKA@mail.gmail.com>
Date: Sat, 2 Apr 2016 19:53:27 -0700
Message-ID: 
 <CAMkU=1z6UFYn8VDREbB1akmGysPNB27uAVNLLQmWUfWUJqRsNA@mail.gmail.com>
Subject: Re: Searching GIN-index (FTS) and sort by timestamp-column
From: Jeff Janes <jeff.janes@gmail.com>
To: Oleg Bartunov <obartunov@gmail.com>
Cc: Andreas Joseph Krogh <andreas@visena.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Tue, Mar 22, 2016 at 9:41 AM, Oleg Bartunov <obartunov@gmail.com> wrote:
>
>
> On Sat, Mar 19, 2016 at 5:44 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>
>>
>> I don't see why it would not be possible to create a new execution node
>> type that does an index scan to obtain order (or just to satisfy an equality
>> or range expression), and takes a bitmap (as produced by the FTS/GIN) to
>> apply as a filter.  But, I don't know of anyone planning on doing that.
>
>
> Please, find bitmap filtering patch, which we developed several months ago,
> but failed  to find good use case :( Teodor is here now, so he could answer
> the questions.

I can't find any benefit because I can't get the new node to ever execute.

I set up this:

create table foo as select md5(random()::text), random() as y from
generate_series(1,10000000);
create index on foo using gin (md5 gin_trgm_ops);
create index on foo (y);
vacuum ANALYZE foo ;

Then when I run this:

explain (analyze,buffers) select y from foo where md5 like '%abcde%'
order by y limit 1

The function "cost_filtered_index(newpath)" never fires.  So the
planner is never even considering this feature.

It seems to be getting short-circuited here:

            if (ipath->indexorderbys == NIL && ipath->indexorderbycols == NIL)
                continue;


I don't know enough about the planner to know where to start on this.

Cheers,

Jeff


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance