Re: Why Postgres doesn't use TID scan?

>>>>> "Vladimir" == Vladimir Ryabtsev <greatvovan(at)gmail(dot)com> writes:

>> The workaround is to do it like this instead:

Vladimir> Strange, I tried to do like this, but the first thing came
Vladimir> into my mind was array_agg() not array():

Vladimir> delete from log
Vladimir> where ctid = any(
Vladimir> select array_agg(ctid) from (
Vladimir> select ctid from log
Vladimir> where timestamp < now() at time zone 'pst' - interval '2 month'
Vladimir> limit 10
Vladimir> ) v);

Vladimir> This query complained like this:

Vladimir> ERROR: operator does not exist: tid = tid[]
Vladimir> LINE 2: where ctid = any(
Vladimir> ^
Vladimir> HINT: No operator matches the given name and argument
Vladimir> type(s). You might need to add explicit type casts.

Vladimir> Which is strange because both array(select ...) and select
Vladimir> array_agg() ... return the same datatype ctid[].

It's not so strange when you understand what's going on here. The
fundamental issue is that "ANY" has two meanings in PG, one of them
following the SQL standard and one not:

x <operator> ANY (<subselect>) -- standard
x <operator> ANY (<expression>) -- PG-specific

In the first case, the behavior follows the standard, which makes this a
generalization of IN: specifically, in the standard,

x IN (select ...)

is just alternative syntax for

x = ANY (select ...)

Obviously in this form, the result of the subselect is expected to be of
the same type and degree as "x", hence the error since tid and tid[] are
not the same type.

(Because this is the standard form, it's the one chosen when the syntax
is otherwise ambiguous between the two.)

The form x = ANY (somearray) is a PG extension, but because of the
ambiguity, the array can only be specified by something that doesn't
parse as a select. So array() works (as does array[] for the commonly
used case of an explicit list), but if you want to use a select to get
the array value, you have to add some kind of syntax that makes it not
parse as a select, e.g.:

WHERE ctid = ANY ((select array_agg(...) from ...)::tid[])

In this case the cast forces it to parse as an expression and not a
subquery (it's not enough to just use the parens alone, because PG,
again unlike the SQL standard, allows any number of excess parens around
a subquery).

--
Andrew (irc:RhodiumToad)