På onsdag 16. mars 2016 kl. 16:07:56, skrev Evgeniy Shishkin < itparan...@gmail.com <mailto:itparan...@gmail.com>>:
> On 16 Mar 2016, at 18:04, Evgeniy Shishkin <itparan...@gmail.com> wrote: > >> >> On 16 Mar 2016, at 17:52, Evgeniy Shishkin <itparan...@gmail.com> wrote: >> >> >>> On 16 Mar 2016, at 16:37, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> >>> Andreas Joseph Krogh <andr...@visena.com> 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 example, >> and if i change folder_id to int from bigint, then index condition with folder_id is used >> >> Index Cond: ((fts_all @@ '''hi'''::tsquery) AND (folder_id = 1)) >> > > Looks like documentation http://www.postgresql.org/docs/9.5/static/btree-gin.html > is lying about supporting int8 type > Uh, it works if i cast to bigint explicitly WHERE del.fts_all @@ to_tsquery('simple', 'hi') AND del.folder_id = 1::bigint; results in Index Cond: ((folder_id = '1'::bigint) AND (fts_all @@ '''hi'''::tsquery)) Hm, this is quite cranky, but thanks for the heads-up! Tho it looks like it works if prepared, without explicit cast: 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_timestampDESC LIMIT 101 OFFSET 0; explain analyze execute fish( 'hi:*', 1); QUERY PLAN ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── Limit (cost=9.13..9.13 rows=1 width=16) (actual time=0.047..0.048 rows=2 loops=1) -> Sort (cost=9.13..9.13 rows=1 width=16) (actual time=0.045..0.045 rows=2 loops=1) Sort Key: received_timestamp DESC Sort Method: quicksort Memory: 25kB -> Bitmap Heap Scan on delivery del (cost=7.80..9.12 rows=1 width=16) (actual time=0.034..0.034 rows=2 loops=1) Recheck Cond: ((fts_all @@ '''hi'':*'::tsquery) AND (folder_id = '1'::bigint)) Heap Blocks: exact=1 -> Bitmap Index Scan on fts_idx (cost=0.00..7.80 rows=1 width=0) (actual time=0.023..0.023 rows=2 loops=1) Index Cond: ((fts_all @@ '''hi'':*'::tsquery) AND (folder_id = '1'::bigint)) Execution time: 0.103 ms (10 rows) -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
