Re: Performance difference between ANY and IN, also array syntax

Bart Grantham <bart@logicworks.net> writes:
> It comes down to this:

> # SELECT * FROM connections  WHERE connectee_node_id = ANY (
> ARRAY[28543,28542] );
> [ performance sucks ]

Yeah :-(.  The = ANY (array) notation is new in the last release or so,
and is completely without optimization of any kind.  Turn it into IN
if you can.

> First, this doesn't work for me: RAISE NOTICE ''DEBUG: %'', _myarray[1];
> It seems pretty reasonable to me, but it gives me a 'syntax error at or
> near "["'.

The RAISE statement is more than a few bricks shy of a load --- it
doesn't accept any sort of expression, only simple variable names.
I hope we get around to fixing that for 8.1, but for now, what you
have to do is assign the thing you want to display into a temporary
variable and then use that in the RAISE.

> Next, I can't seem to declare an array of a user-defined row: _innerrow
> my_type%ROWTYPE[];

We don't presently support arrays of composite types.

You seem to be doing amazingly well at hitting multiple weak spots
of PG simultaneously ;-).  Most of this stuff is on the radar, but
I wouldn't recommend holding your breath for a fix, with the exception
of the RAISE issue.  RAISE is way overdue for an overhaul and I hope
we will actually get to it for 8.1.

            regards, tom lane