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

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

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

On Sat, Mar 19, 2016 at 5:44 AM, Jeff Janes wrote: > On Wed, Mar 16, 2016 at 6:53 AM, Andreas Joseph Krogh > wrote: > >> På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane : >> >> Andreas Joseph Krogh writes: >> > 1. Why isnt' folder_id part of the index-cond? >> >> Because a GIN index is use

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

På mandag 21. mars 2016 kl. 16:13:07, skrev Oleg Bartunov mailto:obartu...@gmail.com>>:     On Mon, Mar 21, 2016 at 5:41 PM, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote: På lørdag 19. mars 2016 kl. 03:44:55, skrev Jeff Janes mailto:jeff.ja...@gmail.com>>: On Wed, Mar 16, 2016 at 6:53 AM

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

On Mon, Mar 21, 2016 at 5:41 PM, Andreas Joseph Krogh wrote: > På lørdag 19. mars 2016 kl. 03:44:55, skrev Jeff Janes < > jeff.ja...@gmail.com>: > > On Wed, Mar 16, 2016 at 6:53 AM, Andreas Joseph Krogh > wrote: >> >> På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane : >> >> Andreas Joseph Kr

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

På lørdag 19. mars 2016 kl. 03:44:55, skrev Jeff Janes mailto:jeff.ja...@gmail.com>>: On Wed, Mar 16, 2016 at 6:53 AM, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote: På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh mailto:andr...@visen

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

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

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

Andreas Joseph Krogh writes: > 1. Why isnt' folder_id part of the index-cond? Because a GIN index is useless for sorting. > 2. Is there a way to make it use the (same) index to sort by > received_timestamp? No. > 3. Using a GIN-index, is there a way to use the index at all for sorting? No.

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

> On 16 Mar 2016, at 16:37, Tom Lane wrote: > > Andreas Joseph Krogh writes: >> 1. Why isnt' folder_id part of the index-cond? > > Because a GIN index is useless for sorting. I don't see how gin inability to return sorted data relates to index condition. In fact i tried to reproduce the examp

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

> On 16 Mar 2016, at 17:52, Evgeniy Shishkin wrote: > > >> On 16 Mar 2016, at 16:37, Tom Lane wrote: >> >> Andreas Joseph Krogh writes: >>> 1. Why isnt' folder_id part of the index-cond? >> >> Because a GIN index is useless for sorting. > > I don't see how gin inability to return sorted da

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

På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > 1. Why isnt' folder_id part of the index-cond? Because a GIN index is useless for sorting. > 2. Is there a way to make it use the (same) index to sort by > received_timestamp? N

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

På onsdag 16. mars 2016 kl. 16:04:08, skrev Evgeniy Shishkin < itparan...@gmail.com >: > On 16 Mar 2016, at 17:52, Evgeniy Shishkin wrote: > > >> On 16 Mar 2016, at 16:37, Tom Lane wrote: >> >> Andreas Joseph Krogh writes: >>> 1. Why isnt' folder_id part of t

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

Evgeniy Shishkin writes: > Uh, it works if i cast to bigint explicitly 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.

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

På onsdag 16. mars 2016 kl. 16:07:56, skrev Evgeniy Shishkin < itparan...@gmail.com >: > On 16 Mar 2016, at 18:04, Evgeniy Shishkin wrote: > >> >> On 16 Mar 2016, at 17:52, Evgeniy Shishkin wrote: >> >> >>> On 16 Mar 2016, at 16:37, Tom Lane wrote: >>> >>>

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

På onsdag 16. mars 2016 kl. 14:53:04, skrev Andreas Joseph Krogh < andr...@visena.com >: På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > 1. Why isnt' folder_id part of the index-cond? Because a GIN index

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

> On 16 Mar 2016, at 18:04, Evgeniy Shishkin wrote: > >> >> On 16 Mar 2016, at 17:52, Evgeniy Shishkin wrote: >> >> >>> On 16 Mar 2016, at 16:37, Tom Lane wrote: >>> >>> Andreas Joseph Krogh writes: 1. Why isnt' folder_id part of the index-cond? >>> >>> Because a GIN index is useles

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

På torsdag 17. mars 2016 kl. 18:20:23, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Evgeniy Shishkin writes: > Uh, it works if i cast to bigint explicitly FWIW, the reason for that is that the int8_ops operator class that btree_gin creates doesn't contain any cross-type operators.  Probably wo

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

On Wed, Mar 16, 2016 at 6:53 AM, Andreas Joseph Krogh wrote: > På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane : > > Andreas Joseph Krogh writes: > > 1. Why isnt' folder_id part of the index-cond? > > Because a GIN index is useless for sorting. > > > 2. Is there a way to make it use the (sa

[PERFORM] Searching GIN-index (FTS) and sort by timestamp-column

Hi all.   I'm doing full-text-search and want do display the results in the order the articles were received (timestamp). I have millions of articles where the newest are the most interesting, and a search may match many articles so doing the sort using some INDEX would be great.   Take the foll