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

From: Tom Lane
Subject: Re: Searching GIN-index (FTS) and sort by timestamp-column
Date: ,
Msg-id: 4024.1458237466@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh)
List: pgsql-performance

Tree view

Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
 Re: Searching GIN-index (FTS) and sort by timestamp-column  (Tom Lane, )
  Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
   Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
   Re: Searching GIN-index (FTS) and sort by timestamp-column  (Jeff Janes, )
    Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
     Re: Searching GIN-index (FTS) and sort by timestamp-column  (Oleg Bartunov, )
      Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
    Re: Searching GIN-index (FTS) and sort by timestamp-column  (Oleg Bartunov, )
     Re: Searching GIN-index (FTS) and sort by timestamp-column  (Jeff Janes, )
  Re: Searching GIN-index (FTS) and sort by timestamp-column  (Evgeniy Shishkin, )
   Re: Searching GIN-index (FTS) and sort by timestamp-column  (Evgeniy Shishkin, )
    Re: Searching GIN-index (FTS) and sort by timestamp-column  (Evgeniy Shishkin, )
     Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
     Re: Searching GIN-index (FTS) and sort by timestamp-column  (Tom Lane, )
      Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
       Re: Searching GIN-index (FTS) and sort by timestamp-column  (Tom Lane, )
    Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )

Andreas Joseph Krogh <> writes:
> På torsdag 17. mars 2016 kl. 18:20:23, skrev Tom Lane <
>  FWIW, the reason for that is that the int8_ops operator class that
>  btree_gin creates doesn't contain any cross-type operators.  Probably
>  wouldn't be that hard to fix if somebody wanted to put in the work.

> Can you explain why it works when using prepared statement without casting?

If you mean the example

prepare fish AS SELECT del.id , del.received_timestamp FROM delivery del
WHERE 1= 1 AND del.fts_all @@ to_tsquery('simple', $1) AND
del.folder_id = $2 ORDER BY del.received_timestamp DESC LIMIT 101 OFFSET 0;

you didn't provide any type for the parameter $2, so the parser had to
infer a type, and the applicable heuristic here is "same type that's on
the other side of the operator".  So you ended up with "bigint = bigint"
which is in the btree_gin operator class.  If you'd specified the
parameter's type as integer, it would've worked the same as Evgeniy's
example.

            regards, tom lane



pgsql-performance by date:

From: "Mike Sofen"
Date:
Subject: Re: Disk Benchmarking Question
From: Albe Laurenz
Date:
Subject: Re: using shared_buffers during seq_scan