Re: Searching GIN-index (FTS) and sort by timestamp-column

On Tue, Mar 22, 2016 at 9:41 AM, Oleg Bartunov <obartunov(at)gmail(dot)com> wrote:
>
>
> On Sat, Mar 19, 2016 at 5:44 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)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