Обсуждение: Why Postgres doesn't use TID scan?

Vladimir Ryabtsev <greatvovan@gmail.com> writes:
> I want to clean a large log table by chunks. I write such a query:
> delete from categorization.log
> where ctid in (
>     select ctid from categorization.log
>     where timestamp < now() - interval '2 month'
>     limit 1000
> )

> Why does this query want to use Seq Scan and Sort on a 423M rows table?

There's no support for using ctid as a join key in this way; specifically,
nodeTidscan.c doesn't have support for being a parameterized inner scan,
nor does tidpath.c have code to generate such a plan.  The header comments
for the latter say

 * There is currently no special support for joins involving CTID; in
 * particular nothing corresponding to best_inner_indexscan().  Since it's
 * not very useful to store TIDs of one table in another table, there
 * doesn't seem to be enough use-case to justify adding a lot of code
 * for that.

Queries like yours are kinda sorta counterexamples to that, but pretty
much all the ones I've seen seem like crude hacks (and this one is not
an exception).  Writing a bunch of code to support them feels like
solving the wrong problem.  Admittedly, it's not clear to me what the
right problem to solve instead would be.

(It's possible that I'm overestimating the amount of new code that would
be needed to implement this, however.  indxpath.c is pretty huge, but
that's mostly because there are so many cases to consider.  There'd only
be one interesting case for an inner TID scan.  Also, this comment is
ancient, predating the current approach with parameterized paths ---
in fact best_inner_indexscan doesn't exist as such anymore.  So maybe
that old judgment that it'd take a lot of added code is wrong.)

            regards, tom lane

Vladimir Ryabtsev <greatvovan@gmail.com> writes:
> I see some recommendations in Internet to do like this (e.g.
> https://stackoverflow.com/questions/5170546/how-do-i-delete-a-fixed-number-of-rows-with-sorting-in-postgresql
> ).
> Did it really work in 2011?

No, or at least not any better than today.  (For context, "git blame"
says I wrote the comment I just quoted to you in 2005.  The feature it
says isn't there wasn't there before that, either.)

> Anyway I think the problem is pretty clear: I want to eventually clear the
> table based on the predicate but I don't want to lock it for a long time.

DELETE doesn't lock the whole table.  What problem are you actually
facing?

            regards, tom lane

On 2018-Dec-17, Tom Lane wrote:

> Queries like yours are kinda sorta counterexamples to that, but pretty
> much all the ones I've seen seem like crude hacks (and this one is not
> an exception).  Writing a bunch of code to support them feels like
> solving the wrong problem.  Admittedly, it's not clear to me what the
> right problem to solve instead would be.

Yeah, over the years I've confronted several times with situations where
a deletion by ctid (and sometimes updates, IIRC) was the most convenient
way out of.  It's not the kind of thing that you'd do with any
frequency, just one-offs.  It's always been a bit embarrasing that this
doesn't "work properly".  There's always been some way around it, much
slower and less convenient ...

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

>>>>> "Vladimir" == Vladimir Ryabtsev <greatvovan@gmail.com> writes:

 Vladimir> I can't believe it.
 Vladimir> I see some recommendations in Internet to do like this

well, 90% of what you read on the Internet is wrong.

 Vladimir> Did it really work in 2011? Are you saying they broke it?
 Vladimir> It's a shame...

The method in that SO link does work, it's just slow. The workaround is
to do it like this instead:

delete from mytable
 where ctid = any (array(select ctid from mytable
                          where ...
                          order by ...
                          limit 1000));

But of course that's still an ugly hack.

-- 
Andrew (irc:RhodiumToad)

>>>>> "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)