From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Vladimir Ryabtsev <greatvovan@gmail.com>
Cc: tgl@sss.pgh.pa.us
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Why Postgres doesn't use TID scan?
Date: Wed, 19 Dec 2018 22:23:15 +0000
Message-ID: <87pntxuqcf.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <CAMqTPq=Pu=gS6bg2BqvZU5D=Vj4Ef1qC8wvNDEbmoMUQ7T_m6A@mail.gmail.com>
References: <CAMqTPq=hNg0GYFU0X+xmuKy8R2ARk1+A_uQpS+Mnf71MYpBKzg@mail.gmail.com>
<31790.1545097237@sss.pgh.pa.us>
<CAMqTPq=4Qp80Qt11DdHjjzM4ksp7O0wTmDQexSUbo2sEoTk+hQ@mail.gmail.com>
<87bm5hwxy2.fsf@news-spur.riddles.org.uk>
<CAMqTPq=Pu=gS6bg2BqvZU5D=Vj4Ef1qC8wvNDEbmoMUQ7T_m6A@mail.gmail.com>
>>>>> "Vladimir" == Vladimir Ryabtsev <greatvovan@gmail.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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@tao11.riddles.org.uk, greatvovan@gmail.com, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: Re: Why Postgres doesn't use TID scan?
In-Reply-To: <87pntxuqcf.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox