Thread

1. Why Postgres doesn't use TID scan?

   Vladimir Ryabtsev <greatvovan@gmail.com> — 2018-12-18T01:16:08Z

   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
   )
   
   But I am getting the following weird plan:
   
   [Plan 1]
   Delete on log  (cost=74988058.17..77101421.77 rows=211334860 width=36)
     ->  Merge Semi Join  (cost=74988058.17..77101421.77 rows=211334860
   width=36)
           Merge Cond: (log.ctid = "ANY_subquery".ctid)
           ->  Sort  (cost=74987967.33..76044641.63 rows=422669720 width=6)
                 Sort Key: log.ctid
                 ->  Seq Scan on log  (cost=0.00..8651368.20 rows=422669720
   width=6)
           ->  Sort  (cost=90.83..93.33 rows=1000 width=36)
                 Sort Key: "ANY_subquery".ctid
                 ->  Subquery Scan on "ANY_subquery"  (cost=0.00..41.00
   rows=1000 width=36)
                       ->  Limit  (cost=0.00..31.00 rows=1000 width=6)
                             ->  Seq Scan on log log_1
   (cost=0.00..11821391.10 rows=381284367 width=6)
                                   Filter: ("timestamp" < (now() - '2
   mons'::interval))
   
   And it takes infinity to complete (with any number in LIMIT from 1 to 1000).
   
   However if I extract CTIDs manually:
   
   select array_agg(ctid) from (
       select ctid from s.log
       where timestamp < now() - interval '2 month'
       limit 5
   ) v
   
   and substitute the result inside the DELETE query, it does basic TID scan
   and completes in just milliseconds:
   
   explain
   delete from s.log
   where ctid =
   any('{"(3020560,1)","(3020560,2)","(3020560,3)","(3020560,4)","(3020560,5)"}'::tid[])
   
   [Plan 2]
   Delete on log  (cost=0.01..20.06 rows=5 width=6)
     ->  Tid Scan on log  (cost=0.01..20.06 rows=5 width=6)
           TID Cond: (ctid = ANY
   ('{"(3020560,1)","(3020560,2)","(3020560,3)","(3020560,4)","(3020560,5)"}'::tid[]))
   
   In case the table's definition helps:
   
   CREATE TABLE s.log
   (
       article_id bigint NOT NULL,
       topic_id integer NOT NULL,
       weight double precision NOT NULL,
       cat_system character varying(50) NOT NULL,
       lang character varying(5) NOT NULL,
       is_final boolean NOT NULL,
       comment character varying(50),
       "timestamp" timestamp without time zone DEFAULT now()
   )
   
   Number of rows ~ 423M
   n_live_tup = 422426725
   last_vacuum = 2018-10-22
   Postgres version(): PostgreSQL 10.3 (Ubuntu 10.3-1.pgdg14.04+1) on
   x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4,
   64-bit
   
   Why does this query want to use Seq Scan and Sort on a 423M rows table?
   How to fix this (reduce it to Plan 2)?
   
   --
   Vlad
   

2. Re: Why Postgres doesn't use TID scan?

   Tom Lane <tgl@sss.pgh.pa.us> — 2018-12-18T01:40:37Z

   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
   
   
   

3. Re: Why Postgres doesn't use TID scan?

   Vladimir Ryabtsev <greatvovan@gmail.com> — 2018-12-18T01:54:19Z

   I can't believe it.
   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? Are you saying they broke it? It's a shame...
   
   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.
   The table does not have a primary key.
   What should be a proper solution?
   
   --
   Vlad
   
   пн, 17 дек. 2018 г. в 17:40, Tom Lane <tgl@sss.pgh.pa.us>:
   
   > 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
   >
   

4. Re: Why Postgres doesn't use TID scan?

   Tom Lane <tgl@sss.pgh.pa.us> — 2018-12-18T02:32:51Z

   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
   
   
   

5. Re: Why Postgres doesn't use TID scan?

   Vladimir Ryabtsev <greatvovan@gmail.com> — 2018-12-18T02:40:49Z

   OK, good to know.
   I saw some timeout errors in the code writing to the log table during my
   DELETE and decided they are relevant. Probably they had nothing to do with
   my actions, need to investigate.
   Thanks anyway.
   
   Best regards,
   Vlad
   
   пн, 17 дек. 2018 г. в 18:32, Tom Lane <tgl@sss.pgh.pa.us>:
   
   >
   > DELETE doesn't lock the whole table.  What problem are you actually
   > facing?
   >
   >
   

6. Re: Why Postgres doesn't use TID scan?

   Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-12-18T16:40:04Z

   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
   
   
   

7. Re: Why Postgres doesn't use TID scan?

   Andrew Gierth <andrew@tao11.riddles.org.uk> — 2018-12-19T11:41:27Z

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

8. Re: Why Postgres doesn't use TID scan?

   Vladimir Ryabtsev <greatvovan@gmail.com> — 2018-12-19T20:22:03Z

   > The workaround is to do it like this instead:
   
   Strange, I tried to do like this, but the first thing came into my
   mind was array_agg()
   not array():
   
   delete from log
   where ctid = any(
       select array_agg(ctid) from (
           select ctid from log
           where timestamp < now() at time zone 'pst' - interval '2 month'
           limit 10
       ) v);
   
   This query complained like this:
   
   ERROR: operator does not exist: tid = tid[]
   LINE 2: where ctid = any(
                      ^
   HINT: No operator matches the given name and argument type(s). You might
   need to add explicit type casts.
   
   Which is strange because both array(select ...) and select array_agg() ...
   return the same datatype ctid[].
   
   > But of course that's still an ugly hack.
   
   Come on... Due to declarative nature of SQL developers sometimes need to
   write much dirtier and uglier hacks.
   This one is just a fluffy hacky.
   
   --
   Vlad
   

9. Re: Why Postgres doesn't use TID scan?

   Andrew Gierth <andrew@tao11.riddles.org.uk> — 2018-12-19T22:23:15Z

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

10. Re: Why Postgres doesn't use TID scan?

    Vladimir Ryabtsev <greatvovan@gmail.com> — 2018-12-19T23:44:58Z

    > The fundamental issue is that "ANY" has two meanings in PG, one of them
    following the SQL standard and one not:
    
    Oh yes, I was aware about two forms but it did not come into my mind, I was
    thinking I use the same form in both cases since my query returns only one
    row and column.
    Thanks for pointing me into that.
    
    --
    Vlad
    

11. Re: Why Postgres doesn't use TID scan?

    Rick Otten <rottenwindfish@gmail.com> — 2018-12-20T13:46:04Z

    On Wed, Dec 19, 2018 at 6:45 PM Vladimir Ryabtsev <greatvovan@gmail.com>
    wrote:
    
    > > The fundamental issue is that "ANY" has two meanings in PG, one of them
    > following the SQL standard and one not:
    >
    > Oh yes, I was aware about two forms but it did not come into my mind, I
    > was thinking I use the same form in both cases since my query returns only
    > one row and column.
    > Thanks for pointing me into that.
    >
    > --
    > Vlad
    >
    
    For what it is worth, I have found that if I am checking for the presence
    of an object in an array, while this syntax is easy to understand and more
    intuitive to craft:
    
        select
          *
        from
          mytable
        where
          ' test' = ANY (my_varchar_array_column)
        ;
    
    This syntax is almost always much faster:
    
        select
            *
        from
            mytable
        where
          ARRAY['test'::varchar] <@ my_varchar_array_column
        ;
    
    (Since this is a performance list after all.)