Re: Performance on Bulk Insert to Partitioned Table

От: Stephen Frost
Тема: Re: Performance on Bulk Insert to Partitioned Table
Дата: ,
Msg-id: 20121228141029.GI16126@tamriel.snowman.net
(см: обсуждение, исходный текст)
Ответ на: Re: Performance on Bulk Insert to Partitioned Table  (Pavel Stehule)
Ответы: Re: Performance on Bulk Insert to Partitioned Table  (Pavel Stehule)
Список: pgsql-performance

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

Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
 Re: Performance on Bulk Insert to Partitioned Table  (Scott Marlowe, )
  Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
   Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
    Re: Performance on Bulk Insert to Partitioned Table  (Jeff Janes, )
 Re: Performance on Bulk Insert to Partitioned Table  (Stephen Frost, )
  Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
 Re: Performance on Bulk Insert to Partitioned Table  (Jeff Janes, )
  Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
   Re: Performance on Bulk Insert to Partitioned Table  (Jeff Janes, )
    Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
 Re: Performance on Bulk Insert to Partitioned Table  (Ondrej Ivanič, )
  Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
   Re: Performance on Bulk Insert to Partitioned Table  (Tom Lane, )
    Re: Performance on Bulk Insert to Partitioned Table  (Scott Marlowe, )
     Re: Performance on Bulk Insert to Partitioned Table  (Jeff Janes, )
      RES: Performance on Bulk Insert to Partitioned Table  ("Luciano Ernesto da Silva", )
       Re: Performance on Bulk Insert to Partitioned Table  (Pavel Stehule, )
    Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
   Re: Performance on Bulk Insert to Partitioned Table  (Jeff Janes, )
    Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
     Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
      Re: Performance on Bulk Insert to Partitioned Table  (Evgeny Shishkin, )
       Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
        Re: Performance on Bulk Insert to Partitioned Table  (Emmanuel Cecchet, )
      Re: Performance on Bulk Insert to Partitioned Table  (Jeff Janes, )
     Re: Performance on Bulk Insert to Partitioned Table  (Jeff Janes, )
      Re: Performance on Bulk Insert to Partitioned Table  (Pavel Stehule, )
      Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
       Performance on Bulk Insert to Partitioned Table  (Jeff Janes, )
        Re: Performance on Bulk Insert to Partitioned Table  (Pavel Stehule, )
         Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
          Re: Performance on Bulk Insert to Partitioned Table  (Ali Pouya, )
           Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )
            Re: Performance on Bulk Insert to Partitioned Table  (Ali Pouya, )
        Re: Performance on Bulk Insert to Partitioned Table  (Stephen Frost, )
         Re: Performance on Bulk Insert to Partitioned Table  (Pavel Stehule, )
         Re: Performance on Bulk Insert to Partitioned Table  (Vitalii Tymchyshyn, )
          Re: Performance on Bulk Insert to Partitioned Table  (Vitalii Tymchyshyn, )
          Re: Performance on Bulk Insert to Partitioned Table  (Stephen Frost, )
           Re: Performance on Bulk Insert to Partitioned Table  (Vitalii Tymchyshyn, )
            Re: Performance on Bulk Insert to Partitioned Table  (Pavel Stehule, )
             Re: Performance on Bulk Insert to Partitioned Table  (Stephen Frost, )
              Re: Performance on Bulk Insert to Partitioned Table  (Pavel Stehule, )
          Re: Performance on Bulk Insert to Partitioned Table  (Pavel Stehule, )
           Re: Performance on Bulk Insert to Partitioned Table  (Vitalii Tymchyshyn, )
          Re: Performance on Bulk Insert to Partitioned Table  (Jeff Janes, )
           Re: Performance on Bulk Insert to Partitioned Table  (Stephen Frost, )
 Re: Performance on Bulk Insert to Partitioned Table  (Charles Gomes, )

2012/12/28 Vitalii Tymchyshyn <>:
> Why so? Basic form "case lvalue when rvalue then out ... end" is much like
> switch.

Sorry, to be honest, I missed that distinction and didn't expect that to
work as-is, yet apparently it does.  Does it currently perform the same
as an if/elsif tree or is it implemented to actually use a table lookup?

* Pavel Stehule () wrote:
> please, look to plpgsql source code. PL/pgSQL is too simply and has
> not own arithmetic unit - all is transformed to SELECTs, has not any
> optimization. But is really short and maintainable.

I was thinking we'd actually do this for all CASE statements, those in
plpgsql and those in regular SQL, if it's possible to do.  Hopefully
it'd be possible to do easily in plpgsql once the SQL-level CASE is
done.

> These SELECTs are evaluated only when it is necessary - but it is
> evaluated by PostgreSQL expression executor - not by PL/pgSQL directly
> - PL/pgSQL cannot process constant by self.

Right, but I wonder if we could pass the entire CASE tree to the
executor, with essentially pointers to the code blocks which will be
executed, and get back a function which we can call over and over that
takes whatever the parameter is and returns the 'right' pointer?

> So any enhancing needs PL/pgSQL redesign and I am not sure, so this
> use case has accurate benefit, because expression bottleneck is only
> one part of partitioning triggers bottleneck. More - if you need
> really fast code, you can use own code in C - and it be 10x times
> faster than any optimized PL/pgSQL code. And using C triggers in
> PostgreSQL is not terrible work.

It's quite a bit of work for people who don't know C or are
(understandably) concerned about writing things which can easily
segfault the entire backend.

> Using plpgsql row triggers for partitioning is not good idea - it is
> just work around from my perspective, and we should to solve source of
> problem - missing native support.

I agree that native partitioning would certainly be nice.  I was really
hoping that was going to happen for 9.3, but it seems unlikely now
(unless I've missed something).

    Thanks,

        Stephen

Вложения

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

От: Pavel Stehule
Дата:
Сообщение: Re: Performance on Bulk Insert to Partitioned Table
От: Stefan Andreatta
Дата:
Сообщение: serious under-estimation of n_distinct for clustered distributions