Re: DELETE with filter on ctid

Список
Период
Сортировка
От Tom Lane
Тема Re: DELETE with filter on ctid
Дата
Msg-id 27253.1176223588@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: DELETE with filter on ctid  ("Spiegelberg, Greg")
Список pgsql-performance
Дерево обсуждения
DELETE with filter on ctid  ("Spiegelberg, Greg", )
 Re: DELETE with filter on ctid  (Tom Lane, )
  Re: DELETE with filter on ctid  ("Spiegelberg, Greg", )
   Re: DELETE with filter on ctid  (Tom Lane, )
 Re: DELETE with filter on ctid  ("Craig A. James", )
  Re: DELETE with filter on ctid  ("Spiegelberg, Greg", )
"Spiegelberg, Greg" <> writes:
> Below is, I believe, everything pertinent to this problem.  First is the
> table in question, second is the problematic and original query, and
> final is the transaction that I have working today with the CTID
> implementation.

So the basic issue here is that data_id_table hasn't got a primary key
you could use as a join key?  I won't lecture you about that, but a lot
of people think it's bad practice not to have a recognizable primary key.

The slow query's problem seems to be mostly that the rowcount estimates
are horribly bad, leading to inappropriate choices of nestloop joins.
Are the statistics up-to-date?  You might try increasing the stats target
for data_id_table in particular.  A really brute-force test would be to
see what happens with that query if you just set enable_nestloop = 0.

As for the CTID query, my initial reaction that you shouldn't need an
index was wrong; looking into the code I see

 * 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.

Maybe we should revisit that sometime, though I'm still not entirely
convinced by this example.

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: Devrim GÜNDÜZ
Дата:
Сообщение: Re: Do I need to rebuild php-pgsql for 8.2.3
Следующее
От: Tobias Brox
Дата:
Сообщение: Long running transactions again ...