From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andreas Joseph Krogh <andreas@visena.com>
cc: Evgeniy Shishkin <itparanoia@gmail.com>,
        pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Searching GIN-index (FTS) and sort by timestamp-column
In-reply-to: <VisenaEmail.ea.7101c678aa3b84c3.153859f7473@tc7-visena>
References: <VisenaEmail.ea.7101c678aa3b84c3.153859f7473@tc7-visena>
Comments: In-reply-to Andreas Joseph Krogh <andreas@visena.com>
	message dated "Thu, 17 Mar 2016 18:30:12 +0100"
Date: Thu, 17 Mar 2016 13:57:46 -0400
Message-ID: <4024.1458237466@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Andreas Joseph Krogh <andreas@visena.com> writes:
> På torsdag 17. mars 2016 kl. 18:20:23, skrev Tom Lane <tgl@sss.pgh.pa.us 
>  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


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