Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT

От: Jochen Erwied
Тема: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Дата: ,
Msg-id: 1628298022.20101205003839@erwied.eu
(см: обсуждение, исходный текст)
Ответ на: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Mladen Gogala)
Ответы: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos)
Список: pgsql-performance

Скрыть дерево обсуждения

Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
 Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
  Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
   Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
    Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
     Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
      Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Mladen Gogala, )
       Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Jochen Erwied, )
        Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
       Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
      Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
       Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
        Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
         Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
          Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
           Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
    Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Mladen Gogala, )

Sunday, December 5, 2010, 12:19:29 AM you wrote:

> Hmmm, what happens if I need 10 years of data, in monthly partitions? It
> would be 120 partitions. Can you please elaborate on that limitation?
> Any plans on lifting that restriction?

I'm running a partitioning scheme using 256 tables with a maximum of 16
million rows (namely IPv4-addresses) and a current total of about 2.5
billion rows, there are no deletes though, but lots of updates.

Using triggers or rules on the main table in my case showed to be not very
effective, so I reverted to updating the inherited tables directly. This
way you still can use a SELECT on the main table letting the optimizer do
it's work, but do not run into the problem of oversized shared memory usage
when doing DELETEs or UPDATEs

IMHO if you are using large partitioning schemes, handle the logic of which
table to update or delete in your application. In most cases extending the
underlying application will be much less work and more flexible than trying
to write a dynamic rule/trigger to do the same job.

--
Jochen Erwied     |   home:      +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 |   work:   +49-2151-7294-24, FAX: -50
D-45470 Muelheim  | mobile:        +49-173-5404164



В списке pgsql-performance по дате сообщения:

От: Jochen Erwied
Дата:
Сообщение: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
От: John Papandriopoulos
Дата:
Сообщение: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT