Arnt,
the problem with the long "ANY" clauses (2.8MB worth of Array values),
is NOT the parsing time in the Postgresql server, whether it is the
value or SQL parser.
The problem is the execution time. If we use UNNEST or the VALUES
syntax, Postgres uses something like a JOIN internally to perform the
search.
My numbers show a 200 fold better execution time with the UNNEST
approach, that should speaks for itself.
This is NOT a parsing issue, it is an execution problem. And for that I
think you can get rid of most of your magic in WhereSet and simply use
the UNNEST trick.
Best
Martin
On 12.05.2015 18:12, Arnt Gulbrandsen wrote:
NSS Ltd writes:
I've also been looking at other uses of 'any' since I think they may
require changed too for performance (and to be consistent) but Arnt may
be better to say if this is the case. I'm making a patch set with those
changed too.
Postgres has two parsers, one for SQL and one for values. (The second
is mostly used with prepared statements.) When you say
select * from mailbox_messages mm where mm.uid=104
then all of that is handled by the SQL parser. When you say
select * from mailbox_messages mm where mm.uid=any{[104,107]}
then all of that is handled by the SQL parser. When you say
select * from mailbox_messages mm where mm.uid=any{$1}
and
$1=[104,107]
then the SQL parser has to handle 54 bytes and the value parser has to
handle about ten bytes (eight or twelve I think, but I don't remember).
Thus, your patch moves load from the value parser to the SQL parser.
The total load on postgres is the same, but a great big weight is
moved from one subsystem to another.
I am deeply uncomfortable with that move, unless Abhijit says it's OK.
(I am also, separately, uncomfortable with the existence of the load.
That's why I added the two if() clauses, both of which eliminate the
load in fairly common cases.)
Arnt
