Re: Why Postgres doesn't use TID scan?

2018-12-20 Thread Rick Otten
On Wed, Dec 19, 2018 at 6:45 PM Vladimir Ryabtsev 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 cas

Re: Why Postgres doesn't use TID scan?

2018-12-19 Thread Vladimir Ryabtsev
> 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 pointi

Re: Why Postgres doesn't use TID scan?

2018-12-19 Thread Andrew Gierth
> "Vladimir" == Vladimir Ryabtsev 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> selec

Re: Why Postgres doesn't use TID scan?

2018-12-19 Thread Vladimir Ryabtsev
> 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'

Re: Why Postgres doesn't use TID scan?

2018-12-19 Thread Andrew Gierth
> "Vladimir" == Vladimir Ryabtsev 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... Th

Re: Why Postgres doesn't use TID scan?

2018-12-18 Thread Alvaro Herrera
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

Re: Why Postgres doesn't use TID scan?

2018-12-17 Thread Vladimir Ryabtsev
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 : > > DELETE doesn't lock

Re: Why Postgres doesn't use TID scan?

2018-12-17 Thread Tom Lane
Vladimir Ryabtsev 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, "gi

Re: Why Postgres doesn't use TID scan?

2018-12-17 Thread Vladimir Ryabtsev
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

Re: Why Postgres doesn't use TID scan?

2018-12-17 Thread Tom Lane
Vladimir Ryabtsev 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

Why Postgres doesn't use TID scan?

2018-12-17 Thread Vladimir Ryabtsev
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=749